Доля возвратов по дням недели в вымышленном мебельном магазине
SQL, по которому посчитано
WITH last_date AS (
SELECT max(date) AS max_date
FROM sales
),
period AS (
SELECT s.date, s.units, s.returned_units
FROM sales s
CROSS JOIN last_date l
WHERE s.date >= l.max_date - INTERVAL '12 months'
),
weekday_stats AS (
SELECT
CASE dayofweek(date)
WHEN 1 THEN 'Понедельник'
WHEN 2 THEN 'Вторник'
WHEN 3 THEN 'Среда'
WHEN 4 THEN 'Четверг'
WHEN 5 THEN 'Пятница'
WHEN 6 THEN 'Суббота'
WHEN 0 THEN 'Воскресенье'
END AS weekday,
CASE dayofweek(date)
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4
WHEN 5 THEN 5
WHEN 6 THEN 6
WHEN 0 THEN 7
END AS weekday_order,
sum(returned_units) AS returned_units,
sum(units) AS sold_units
FROM period
GROUP BY 1, 2
)
SELECT
weekday,
returned_units,
sold_units,
round(returned_units::DOUBLE / nullif(sold_units, 0), 4) AS return_share
FROM weekday_stats
ORDER BY weekday_order