Прибыль по дням недели: как сравнить дни за 12 месяцев

SQL, по которому посчитано

WITH bounds AS (
    SELECT
        date_trunc('month', max(date)) - INTERVAL 11 MONTH AS start_date,
        max(date) AS end_date
    FROM sales
),
sales_daily AS (
    SELECT
        s.date,
        SUM(s.revenue - s.returned_revenue) AS net_revenue,
        SUM(s.cost_of_goods) AS cogs,
        SUM(s.commission) AS commission,
        SUM(s.logistics) AS logistics
    FROM sales s
    CROSS JOIN bounds b
    WHERE s.date >= b.start_date AND s.date <= b.end_date
    GROUP BY s.date
),
ads_daily AS (
    SELECT
        a.date,
        SUM(a.cost) AS ad_cost
    FROM ads a
    CROSS JOIN bounds b
    WHERE a.date >= b.start_date AND a.date <= b.end_date
    GROUP BY a.date
),
days AS (
    SELECT date FROM sales_daily
    UNION
    SELECT date FROM ads_daily
),
daily AS (
    SELECT
        d.date,
        COALESCE(s.net_revenue, 0) AS net_revenue,
        COALESCE(s.cogs, 0) AS cogs,
        COALESCE(s.commission, 0) AS commission,
        COALESCE(s.logistics, 0) AS logistics,
        COALESCE(a.ad_cost, 0) AS ad_cost,
        COALESCE(s.net_revenue, 0) - COALESCE(s.cogs, 0) - COALESCE(s.commission, 0) - COALESCE(s.logistics, 0) - COALESCE(a.ad_cost, 0) AS profit,
        CASE dayofweek(d.date) WHEN 0 THEN 7 ELSE dayofweek(d.date) END AS weekday_num
    FROM days d
    LEFT JOIN sales_daily s ON s.date = d.date
    LEFT JOIN ads_daily a ON a.date = d.date
)
SELECT
    CASE weekday_num
        WHEN 1 THEN 'Понедельник'
        WHEN 2 THEN 'Вторник'
        WHEN 3 THEN 'Среда'
        WHEN 4 THEN 'Четверг'
        WHEN 5 THEN 'Пятница'
        WHEN 6 THEN 'Суббота'
        WHEN 7 THEN 'Воскресенье'
    END AS weekday,
    COUNT(*) AS days_count,
    ROUND(SUM(net_revenue), 2) AS revenue,
    ROUND(SUM(profit), 2) AS profit,
    ROUND(SUM(profit) / NULLIF(SUM(net_revenue), 0), 4) AS margin,
    ROUND(AVG(profit), 2) AS avg_profit
FROM daily
GROUP BY weekday_num
ORDER BY weekday_num