-- 曜日ごとの売上分析クエリ
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,
        ROUND(IFNULL(SUM(s.SalesCount), 0) / 4.0) AS AVG_CNT,
        ROUND(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,
        AVG_CNT,
        AVG_AMT,
        CASE
            WHEN AVG_AMT >= 100000000 THEN
                substr(cast(AVG_AMT AS TEXT), 1, length(cast(AVG_AMT AS TEXT)) - 8) || ',' ||
                substr(cast(AVG_AMT AS TEXT), length(cast(AVG_AMT AS TEXT)) - 7, 3) || ',' ||
                substr(cast(AVG_AMT AS TEXT), length(cast(AVG_AMT AS TEXT)) - 4, 3) || ',' ||
                substr(cast(AVG_AMT AS TEXT), length(cast(AVG_AMT AS TEXT)) - 1, 3) || '円'
            WHEN AVG_AMT >= 1000000 THEN
                substr(cast(AVG_AMT AS TEXT), 1, length(cast(AVG_AMT AS TEXT)) - 6) || ',' ||
                substr(cast(AVG_AMT AS TEXT), length(cast(AVG_AMT AS TEXT)) - 5, 3) || ',' ||
                substr(cast(AVG_AMT AS TEXT), length(cast(AVG_AMT AS TEXT)) - 2, 3) || '円'
            WHEN AVG_AMT >= 1000 THEN
                substr(cast(AVG_AMT AS TEXT), 1, length(cast(AVG_AMT AS TEXT)) - 3) || ',' ||
                substr(cast(AVG_AMT AS TEXT), length(cast(AVG_AMT AS TEXT)) - 2, 3) || '円'
            ELSE
                cast(AVG_AMT AS TEXT) || '円'
        END AS Formatted_AVG_AMT
    FROM (
        SELECT
            Week,
            AVG_CNT,
            CAST(AVG_AMT AS INTEGER) AS AVG_AMT -- .0を除去するため整数に変換
        FROM Averages
    )
)
SELECT
    Week,
    AVG_CNT,
    Formatted_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;