Изменение числа заказов с рекламы по кампаниям, последний месяц к предыдущему
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date)) AS cur_start,
date_trunc('month', max(date)) - INTERVAL 1 MONTH AS prev_start
FROM ads
),
monthly AS (
SELECT
a.campaign,
sum(CASE WHEN a.date >= b.prev_start AND a.date < b.cur_start THEN a.orders ELSE 0 END) AS orders_prev_month,
sum(CASE WHEN a.date >= b.cur_start AND a.date < b.cur_start + INTERVAL 1 MONTH THEN a.orders ELSE 0 END) AS orders_last_month
FROM ads AS a
CROSS JOIN bounds AS b
WHERE a.date >= b.prev_start
AND a.date < b.cur_start + INTERVAL 1 MONTH
GROUP BY a.campaign
)
SELECT
campaign,
orders_prev_month,
orders_last_month,
orders_last_month - orders_prev_month AS orders_change
FROM monthly
WHERE orders_prev_month > 0
ORDER BY orders_change ASC
LIMIT 30