Товары с высокой маржой и низкими продажами за 12 месяцев
| Артикул | Товар | Категория | Продано штук с учетом возвратов | Маржа, руб. | Маржинальность | Выручка после возвратов, руб. |
|---|---|---|---|---|---|---|
| KU-301 | Кухонный гарнитур Ханко | Кухни | 293 | 9 666 578 ₽ | 39,1 % | 24 742 905 ₽ |
| SH-401 | Шкаф-купе Бергамо | Шкафы | 455 | 6 227 043 ₽ | 30,6 % | 20 333 976 ₽ |
| KR-202 | Кровать Кальмар 180 | Кровати | 499 | 5 972 140 ₽ | 30,3 % | 19 721 590 ₽ |
| DV-101 | Диван угловой Осло | Диваны | 627 | 11 476 166 ₽ | 29,6 % | 38 719 882 ₽ |
SQL, по которому посчитано
WITH bounds AS (
SELECT
(date_trunc('month', max(date)) - INTERVAL '11 months')::DATE AS start_date,
max(date) AS end_date
FROM sales
),
sales_period AS (
SELECT
s.sku,
s.product,
s.category,
s.units,
s.returned_units,
s.revenue,
s.returned_revenue,
s.cost_of_goods,
s.commission,
s.logistics
FROM sales AS s
CROSS JOIN bounds AS b
WHERE s.date BETWEEN b.start_date AND b.end_date
),
sku_metrics AS (
SELECT
sku,
any_value(product) AS product,
any_value(category) AS category,
sum(units - returned_units) AS net_units,
round(sum(revenue - returned_revenue), 2) AS net_revenue,
round(sum(revenue - returned_revenue - cost_of_goods - commission - logistics), 2) AS margin_rub,
round(
sum(revenue - returned_revenue - cost_of_goods - commission - logistics)
/ nullif(sum(revenue - returned_revenue), 0),
4
) AS margin_rate
FROM sales_period
GROUP BY sku
HAVING sum(units - returned_units) > 0
AND sum(revenue - returned_revenue) > 0
AND sum(revenue - returned_revenue - cost_of_goods - commission - logistics) > 0
),
thresholds AS (
SELECT
median(margin_rate) AS median_margin,
quantile_cont(net_units, 0.25) AS low_sales_threshold
FROM sku_metrics
)
SELECT
m.sku,
m.product,
m.category,
m.net_units,
m.net_revenue,
m.margin_rub,
m.margin_rate
FROM sku_metrics AS m
CROSS JOIN thresholds AS t
WHERE m.margin_rate >= t.median_margin
AND m.net_units <= t.low_sales_threshold
ORDER BY m.margin_rate DESC, m.margin_rub DESC
LIMIT 30