Доля выручки в выходные и будни по каналам
SQL, по которому посчитано
WITH daily AS (
SELECT
channel,
date,
CASE WHEN isodow(date) >= 6 THEN 'выходные' ELSE 'будни' END AS day_type,
sum(revenue - coalesce(returned_revenue, 0)) AS revenue
FROM sales
WHERE date >= (SELECT max(date) - INTERVAL '12 months' FROM sales)
GROUP BY channel, date, isodow(date) >= 6
),
by_type AS (
SELECT
channel,
day_type,
sum(revenue) AS revenue
FROM daily
GROUP BY channel, day_type
),
by_channel AS (
SELECT
channel,
sum(revenue) AS revenue
FROM by_type
GROUP BY channel
)
SELECT
b.channel,
b.day_type,
round(b.revenue, 2) AS revenue,
round(b.revenue / nullif(c.revenue, 0), 4) AS share
FROM by_type b
JOIN by_channel c ON b.channel = c.channel
ORDER BY b.channel, b.day_type