Средняя цена единицы по артикулам за 12 месяцев
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
),
sku_sales AS (
SELECT
s.sku,
any_value(s.product) AS product,
sum(s.revenue - s.returned_revenue) AS net_revenue,
sum(s.units - s.returned_units) AS net_units
FROM sales AS s
CROSS JOIN bounds AS b
WHERE s.date >= b.start_date
AND s.date < b.end_date
GROUP BY s.sku
HAVING sum(s.units - s.returned_units) > 0
)
SELECT
sku,
product,
round(net_revenue / net_units, 2) AS avg_price,
net_units,
round(net_revenue, 2) AS net_revenue
FROM sku_sales
ORDER BY net_revenue DESC
LIMIT 30