Корреляция цены и продаж по артикулам
SQL, по которому посчитано
WITH bounds AS (
SELECT
date_trunc('month', max(date)) - INTERVAL '11 months' AS start_date,
date_trunc('month', max(date)) + INTERVAL '1 month' AS end_date
FROM sales
),
monthly AS (
SELECT
s.sku,
s.product,
date_trunc('month', s.date) AS month,
sum(s.revenue - s.returned_revenue) AS net_revenue,
sum(s.units - s.returned_units) AS net_units
FROM sales s
CROSS JOIN bounds b
WHERE s.date >= b.start_date AND s.date < b.end_date
GROUP BY s.sku, s.product, date_trunc('month', s.date)
HAVING sum(s.units - s.returned_units) > 0
),
priced AS (
SELECT
sku,
product,
month,
net_revenue / net_units AS avg_price,
net_units
FROM monthly
),
ranked AS (
SELECT
sku,
product,
count(*) AS months,
corr(avg_price, net_units) AS corr_price_units
FROM priced
GROUP BY sku, product
HAVING count(*) >= 6
AND count(DISTINCT avg_price) > 1
AND count(DISTINCT net_units) > 1
)
SELECT
sku,
product,
months,
round(corr_price_units, 3) AS corr_price_units
FROM ranked
WHERE corr_price_units IS NOT NULL
ORDER BY abs(corr_price_units) DESC, months DESC, sku
LIMIT 30