Прибыль на единицу по каналам, вымышленный мебельный магазин
SQL, по которому посчитано
WITH sales_month AS (
SELECT
channel,
units,
revenue,
cost_of_goods,
commission,
logistics,
returned_units,
returned_revenue
FROM sales
WHERE date >= (SELECT date_trunc('month', max(date)) FROM sales)
AND date < (SELECT date_trunc('month', max(date)) + INTERVAL 1 MONTH FROM sales)
),
ad_month AS (
SELECT
CASE source
WHEN 'Яндекс Директ' THEN 'Сайт'
WHEN 'VK Реклама' THEN 'Сайт'
WHEN 'Продвижение WB' THEN 'Wildberries'
WHEN 'Продвижение Ozon' THEN 'Ozon'
END AS channel,
SUM(cost) AS ad_cost
FROM ads
WHERE date >= (SELECT date_trunc('month', max(date)) FROM sales)
AND date < (SELECT date_trunc('month', max(date)) + INTERVAL 1 MONTH FROM sales)
GROUP BY 1
),
channel_calc AS (
SELECT
channel,
SUM(units - returned_units) AS net_units,
SUM(revenue - returned_revenue) AS net_revenue,
SUM(cost_of_goods * (1 - COALESCE(returned_units::DOUBLE / NULLIF(units, 0), 0))) AS net_cogs,
SUM(commission) AS commission,
SUM(logistics) AS logistics
FROM sales_month
GROUP BY channel
)
SELECT
c.channel,
ROUND((c.net_revenue - c.net_cogs - c.commission - c.logistics - COALESCE(a.ad_cost, 0)) / NULLIF(c.net_units, 0), 2) AS profit_per_unit
FROM channel_calc c
LEFT JOIN ad_month a USING (channel)
WHERE c.net_units > 0
ORDER BY profit_per_unit DESC