Вклад факторов в изменение прибыли по каналам за 12 месяцев
SQL, по которому посчитано
WITH cur AS (
SELECT channel,
sum(revenue - returned_revenue) AS net_revenue,
sum(cost_of_goods) AS cogs,
sum(commission) AS commission,
sum(logistics) AS logistics
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY channel
),
prev AS (
SELECT channel,
sum(revenue - returned_revenue) AS net_revenue,
sum(cost_of_goods) AS cogs,
sum(commission) AS commission,
sum(logistics) AS logistics
FROM sales
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 24 MONTH
AND date <= (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
GROUP BY channel
),
joined AS (
SELECT coalesce(c.channel, p.channel) AS channel,
coalesce(c.net_revenue, 0) - coalesce(p.net_revenue, 0) AS d_revenue,
coalesce(c.cogs, 0) - coalesce(p.cogs, 0) AS d_cogs,
coalesce(c.commission, 0) - coalesce(p.commission, 0) AS d_commission,
coalesce(c.logistics, 0) - coalesce(p.logistics, 0) AS d_logistics
FROM cur c
FULL OUTER JOIN prev p ON c.channel = p.channel
)
SELECT channel, 'Выручка' AS factor, round(d_revenue, 0) AS effect FROM joined
UNION ALL
SELECT channel, 'Себестоимость', round(-d_cogs, 0) FROM joined
UNION ALL
SELECT channel, 'Комиссия площадки', round(-d_commission, 0) FROM joined
UNION ALL
SELECT channel, 'Логистика', round(-d_logistics, 0) FROM joined
ORDER BY channel, factor