Структура прибыли по каналам за последние три месяца
SQL, по которому посчитано
WITH period AS (
SELECT
date_trunc('month', max(date)) AS last_month,
date_trunc('month', max(date)) - INTERVAL '2 month' AS start_month
FROM sales
),
sales_month AS (
SELECT
date_trunc('month', s.date) AS month,
s.channel,
sum(s.revenue - s.returned_revenue - s.cost_of_goods - s.commission - s.logistics) AS profit_before_ads
FROM sales s
CROSS JOIN period p
WHERE s.date >= p.start_month
AND s.date < p.last_month + INTERVAL '1 month'
GROUP BY 1, 2
),
ads_month AS (
SELECT
date_trunc('month', a.date) AS month,
CASE
WHEN a.source = 'Продвижение WB' THEN 'Wildberries'
WHEN a.source = 'Продвижение Ozon' THEN 'Ozon'
ELSE 'Сайт'
END AS channel,
sum(a.cost) AS ad_cost
FROM ads a
CROSS JOIN period p
WHERE a.date >= p.start_month
AND a.date < p.last_month + INTERVAL '1 month'
GROUP BY 1, 2
),
all_months_channels AS (
SELECT month, channel FROM sales_month
UNION
SELECT month, channel FROM ads_month
)
SELECT
amc.month,
amc.channel,
round(coalesce(sm.profit_before_ads, 0) - coalesce(adm.ad_cost, 0), 0) AS profit
FROM all_months_channels amc
LEFT JOIN sales_month sm ON sm.month = amc.month AND sm.channel = amc.channel
LEFT JOIN ads_month adm ON adm.month = amc.month AND adm.channel = amc.channel
ORDER BY amc.month, amc.channel