Доля возвратов по категориям за 12 месяцев
SQL, по которому посчитано
WITH bounds AS (
SELECT date_trunc('month', max(date)) - INTERVAL '11 months' AS start_date
FROM sales
),
agg AS (
SELECT s.category,
SUM(s.units) AS sold_units,
SUM(s.returned_units) AS returned_units,
SUM(s.revenue) AS revenue,
SUM(s.returned_revenue) AS returned_revenue
FROM sales s
CROSS JOIN bounds b
WHERE s.date >= b.start_date
GROUP BY s.category
)
SELECT category,
sold_units,
returned_units,
round(returned_units * 1.0 / NULLIF(sold_units, 0), 4) AS return_share_units,
round(returned_revenue / NULLIF(revenue, 0), 4) AS return_share_revenue
FROM agg
ORDER BY return_share_units DESC
LIMIT 30