Выручка продаж и рекламы по каналам за последний квартал
SQL, по которому посчитано
WITH bounds AS (
SELECT CAST(date_trunc('quarter', max(date)) AS DATE) AS period_start,
max(date) AS period_end
FROM ads
),
sales_agg AS (
SELECT s.channel,
SUM(s.revenue - s.returned_revenue) AS sales_revenue
FROM sales s
CROSS JOIN bounds b
WHERE s.date >= b.period_start AND s.date <= b.period_end
GROUP BY s.channel
),
ads_agg AS (
SELECT CASE a.source
WHEN 'Яндекс Директ' THEN 'Сайт'
WHEN 'VK Реклама' THEN 'Сайт'
WHEN 'Продвижение WB' THEN 'Wildberries'
WHEN 'Продвижение Ozon' THEN 'Ozon'
END AS channel,
SUM(a.revenue) AS ad_revenue
FROM ads a
CROSS JOIN bounds b
WHERE a.date >= b.period_start AND a.date <= b.period_end
GROUP BY 1
)
SELECT COALESCE(s.channel, a.channel) AS channel,
ROUND(COALESCE(s.sales_revenue, 0), 2) AS sales_revenue,
ROUND(COALESCE(a.ad_revenue, 0), 2) AS ad_revenue,
ROUND(COALESCE(a.ad_revenue, 0) - COALESCE(s.sales_revenue, 0), 2) AS revenue_gap
FROM sales_agg s
FULL OUTER JOIN ads_agg a ON s.channel = a.channel
ORDER BY channel