Доля комиссии маркетплейса в прибыли по категориям, последний месяц, вымышленный мебельный магазин
SQL, по которому посчитано
WITH last_month AS (
SELECT date_trunc('month', max(date)) AS month_start
FROM sales
),
marketplace_sales AS (
SELECT
s.category,
SUM(s.revenue - s.returned_revenue) AS net_revenue,
SUM(s.cost_of_goods) AS cost_of_goods,
SUM(s.commission) AS commission,
SUM(s.logistics) AS logistics
FROM sales s
CROSS JOIN last_month lm
WHERE s.channel IN ('Wildberries', 'Ozon', 'Яндекс Маркет')
AND s.date >= lm.month_start
AND s.date < lm.month_start + INTERVAL '1 month'
GROUP BY s.category
)
SELECT
category,
round(commission / nullif(net_revenue - cost_of_goods - commission - logistics, 0), 4) AS commission_share
FROM marketplace_sales
ORDER BY commission_share DESC NULLS LAST