Рост продаж год к году по каналам
SQL, по которому посчитано
WITH b AS (
SELECT max(date) AS d FROM sales
),
s AS (
SELECT channel,
CASE WHEN date > (SELECT d FROM b) - INTERVAL 12 MONTH THEN 'last' ELSE 'prev' END AS period,
revenue
FROM sales
WHERE date > (SELECT d FROM b) - INTERVAL 24 MONTH
)
SELECT channel,
round(sum(CASE WHEN period = 'last' THEN revenue ELSE 0 END), 0) AS revenue_last,
round(sum(CASE WHEN period = 'prev' THEN revenue ELSE 0 END), 0) AS revenue_prev,
round(sum(CASE WHEN period = 'last' THEN revenue ELSE 0 END) / nullif(sum(CASE WHEN period = 'prev' THEN revenue ELSE 0 END), 0) - 1, 4) AS growth
FROM s
GROUP BY channel
ORDER BY growth DESC NULLS LAST