Выручка по дням недели за 12 месяцев
SQL, по которому посчитано
WITH bounds AS (
SELECT max(date) AS max_date
FROM sales
),
period AS (
SELECT
date,
date_part('isodow', date) AS dow_num,
revenue,
units
FROM sales, bounds
WHERE date > bounds.max_date - INTERVAL 12 MONTH
)
SELECT
dow_num,
CASE dow_num
WHEN 1 THEN 'Понедельник'
WHEN 2 THEN 'Вторник'
WHEN 3 THEN 'Среда'
WHEN 4 THEN 'Четверг'
WHEN 5 THEN 'Пятница'
WHEN 6 THEN 'Суббота'
ELSE 'Воскресенье'
END AS day_name,
round(sum(revenue), 0) AS revenue,
sum(units) AS units,
round(sum(revenue) / count(DISTINCT date), 0) AS avg_day_revenue
FROM period
GROUP BY dow_num
ORDER BY dow_num