Validação.sql

WITH daily_sales AS (
    SELECT
        p.producer_id,
        EXTRACT(DOW FROM s.purchase_date::DATE) AS day_of_week,
        EXTRACT(YEAR FROM s.purchase_date::DATE) AS year,
        COUNT(s.purchase_id) AS total_sales
    FROM
        "case".sales s
    JOIN "case".products p ON
        p.product_id = s.product_id
    JOIN "case".producers p2 ON
        p2.producer_id = p.producer_id
    WHERE
        p2.country = 'BRASIL'
        AND s.purchase_date::DATE BETWEEN '2020-01-01' AND '2022-12-31'
        AND s.refund = 0
        AND s.cancelled = 0
        AND s.chargeback = 0
    GROUP BY
        p.producer_id,
        EXTRACT(DOW FROM s.purchase_date::DATE),
        EXTRACT(YEAR FROM s.purchase_date::DATE)
),
weekly_variation AS (
    SELECT
        day_of_week,
        year,
        SUM(total_sales) AS total_sales
    FROM
        daily_sales
    GROUP BY
        day_of_week,
        year
), 
Ano_1 as(
SELECT
    CASE
        WHEN w1.day_of_week = 0 THEN 'Sunday'
        WHEN w1.day_of_week = 1 THEN 'Monday'
        WHEN w1.day_of_week = 2 THEN 'Tuesday'
        WHEN w1.day_of_week = 3 THEN 'Wednesday'
        WHEN w1.day_of_week = 4 THEN 'Thursday'
        WHEN w1.day_of_week = 5 THEN 'Friday'
        WHEN w1.day_of_week = 6 THEN 'Saturday'
    END AS day_name,
    w1.year AS base_year,
    w2.year AS comparison_year,
    w1.total_sales AS base_year_sales,
    COALESCE(w2.total_sales, 0) AS comparison_year_sales,
    COALESCE((w1.total_sales - w2.total_sales), 0) AS sales_variation
FROM
    weekly_variation w1
LEFT JOIN weekly_variation w2 ON
    w1.day_of_week = w2.day_of_week
    AND w1.year = w2.year + 1
WHERE
    w1.year IN (2022, 2021)
ORDER BY
    w1.day_of_week,
    w1.year
),
Anos_2 as(
SELECT
    CASE
        WHEN w1.day_of_week = 0 THEN 'Sunday'
        WHEN w1.day_of_week = 1 THEN 'Monday'
        WHEN w1.day_of_week = 2 THEN 'Tuesday'
        WHEN w1.day_of_week = 3 THEN 'Wednesday'
        WHEN w1.day_of_week = 4 THEN 'Thursday'
        WHEN w1.day_of_week = 5 THEN 'Friday'
        WHEN w1.day_of_week = 6 THEN 'Saturday'
    END AS day_name,
    w1.year AS base_year,
    w2.year AS comparison_year,
    w1.total_sales AS base_year_sales,
    COALESCE(w2.total_sales, 0) AS comparison_year_sales,
    COALESCE((w1.total_sales - w2.total_sales), 0) AS sales_variation
FROM
    weekly_variation w1
LEFT JOIN weekly_variation w2 ON
    w1.day_of_week = w2.day_of_week
    AND w1.year = w2.year + 1
WHERE
    w1.year IN (2022, 2021)
ORDER BY
    w1.day_of_week,
    w1.year
)
Select * from ano_1
Union all
Select * from ano_2