План и факт рекламного бюджета по каналам за 12 месяцев
SQL, по которому посчитано
WITH plan_agg AS (
SELECT
channel,
SUM(ad_cost) AS plan_ad_cost
FROM plan
WHERE month > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
AND month <= (SELECT max(date) FROM sales)
GROUP BY channel
),
fact_agg AS (
SELECT
CASE source
WHEN 'Яндекс Директ' THEN 'Сайт'
WHEN 'VK Реклама' THEN 'Сайт'
WHEN 'Продвижение WB' THEN 'Wildberries'
WHEN 'Продвижение Ozon' THEN 'Ozon'
END AS channel,
SUM(cost) AS fact_ad_cost
FROM ads
WHERE date > (SELECT max(date) FROM sales) - INTERVAL 12 MONTH
AND date <= (SELECT max(date) FROM sales)
GROUP BY 1
),
joined AS (
SELECT
COALESCE(p.channel, f.channel) AS channel,
COALESCE(p.plan_ad_cost, 0) AS plan_ad_cost,
COALESCE(f.fact_ad_cost, 0) AS fact_ad_cost
FROM plan_agg p
FULL OUTER JOIN fact_agg f ON p.channel = f.channel
)
SELECT
channel,
round(plan_ad_cost, 0) AS plan_ad_cost,
round(fact_ad_cost, 0) AS fact_ad_cost,
round(fact_ad_cost - plan_ad_cost, 0) AS deviation,
round(fact_ad_cost / NULLIF(plan_ad_cost, 0), 3) AS execution_rate
FROM joined
WHERE channel IS NOT NULL
ORDER BY channel