Падение выручки по артикулам, последний месяц к предыдущему
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date)) AS last_month,
CAST(date_trunc('month', max(date)) - INTERVAL 1 MONTH AS DATE) AS prev_month
FROM sales
),
monthly AS (
SELECT
s.sku,
max(s.product) AS product,
sum(CASE WHEN s.date >= b.last_month THEN s.units ELSE 0 END) AS units_last,
sum(CASE WHEN s.date < b.last_month THEN s.units ELSE 0 END) AS units_prev,
sum(CASE WHEN s.date >= b.last_month THEN s.revenue ELSE 0 END) AS revenue_last,
sum(CASE WHEN s.date < b.last_month THEN s.revenue ELSE 0 END) AS revenue_prev
FROM sales AS s
CROSS JOIN bounds AS b
WHERE s.date >= b.prev_month
GROUP BY s.sku
)
SELECT
sku,
product,
units_prev,
units_last,
units_last - units_prev AS units_change,
round(revenue_prev, 2) AS revenue_prev,
round(revenue_last, 2) AS revenue_last,
round(revenue_last - revenue_prev, 2) AS revenue_change,
round((revenue_last - revenue_prev) / nullif(revenue_prev, 0), 4) AS revenue_change_share
FROM monthly
WHERE revenue_prev > 0 AND revenue_last < revenue_prev
ORDER BY revenue_change ASC
LIMIT 20