-- 曜日ごとの売上分析クエリ
WITH SalesDays AS (
    SELECT
        strftime('%w', SALES_DATE) AS DayOfWeek,
        COUNT(*) AS SalesCount,
        SUM(SALES_AMT) AS SalesAmountTotal
    FROM SALES
    WHERE
        SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
        AND SALES_TYPE <> 2
        AND UPDATED_NO IS NULL
    GROUP BY DayOfWeek
),
DaysOfWeek AS (
    SELECT '0' AS DayOfWeek, '日' AS Week UNION
    SELECT '1', '月' UNION
    SELECT '2', '火' UNION
    SELECT '3', '水' UNION
    SELECT '4', '木' UNION
    SELECT '5', '金' UNION
    SELECT '6', '土'
),
Averages AS (
    SELECT
        d.Week,
        IFNULL(SUM(s.SalesCount), 0) / 4.0 AS AVG_CNT,
        IFNULL(SUM(s.SalesAmountTotal), 0) / 4.0 AS AVG_AMT
    FROM DaysOfWeek d
    LEFT JOIN SalesDays s ON d.DayOfWeek = s.DayOfWeek
    GROUP BY d.Week
),
FormattedAverages AS (
    SELECT
        Week,
        ROUND(AVG_CNT) AS AVG_CNT,
        ROUND(AVG_AMT, 0) AS AVG_AMT
    FROM Averages
)
SELECT
    Week,
    AVG_CNT,
    printf('%,d', AVG_AMT) || '円' AS AVG_AMT
FROM FormattedAverages
ORDER BY
    CASE Week
        WHEN '日' THEN 1
        WHEN '月' THEN 2
        WHEN '火' THEN 3
        WHEN '水' THEN 4
        WHEN '木' THEN 5
        WHEN '金' THEN 6
        WHEN '土' THEN 7
    END;