Маржа убыточных артикулов на маркетплейсах за последний квартал
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('quarter', max(date))::DATE AS start_date,
max(date) AS end_date
FROM sales
),
sku_margin AS (
SELECT
s.sku,
max(s.product) AS product,
max(s.category) AS category,
sum(s.units) AS units,
round(sum(s.revenue - s.returned_revenue), 0) AS net_revenue,
round(sum(s.revenue - s.returned_revenue - s.cost_of_goods - s.commission - s.logistics), 0) AS margin,
round(
sum(s.revenue - s.returned_revenue - s.cost_of_goods - s.commission - s.logistics)
/ nullif(sum(s.revenue - s.returned_revenue), 0), 4
) AS margin_rate
FROM sales AS s
CROSS JOIN bounds AS b
WHERE s.channel <> 'Сайт'
AND s.date >= b.start_date
AND s.date <= b.end_date
GROUP BY s.sku
HAVING sum(s.revenue - s.returned_revenue - s.cost_of_goods - s.commission - s.logistics) < 0
)
SELECT
sku,
product,
category,
units,
net_revenue,
margin,
margin_rate
FROM sku_margin
ORDER BY margin ASC
LIMIT 30