Доля маржи по каналам и категориям за 12 месяцев
SQL, по которому посчитано
WITH s AS (
SELECT channel, category, units, revenue, cost_of_goods, commission, logistics, returned_units, returned_revenue
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
),
agg AS (
SELECT
channel,
category,
sum(revenue - returned_revenue) AS net_revenue,
sum(revenue - returned_revenue
- cost_of_goods * (units - returned_units) / NULLIF(units, 0)
- commission - logistics) AS margin
FROM s
GROUP BY channel, category
)
SELECT
channel,
category,
round(net_revenue, 0) AS net_revenue,
round(margin, 0) AS margin,
round(margin / NULLIF(net_revenue, 0), 4) AS margin_rate
FROM agg
ORDER BY channel, margin_rate DESC