WITH Weekdays AS (
    SELECT '0' AS weekday, '日' AS week_name, 4 AS days_count  -- 3月2024では日曜日が4回
    UNION ALL SELECT '1', '月', 4
    UNION ALL SELECT '2', '火', 4
    UNION ALL SELECT '3', '水', 4
    UNION ALL SELECT '4', '木', 4
    UNION ALL SELECT '5', '金', 4
    UNION ALL SELECT '6', '土', 4
),
FilteredData AS (
    SELECT
        strftime('%w', sales_date) AS weekday,
        COUNT(*) AS cnt,
        SUM(sales_amt) AS total_amt
    FROM SALES
    WHERE
        sales_date BETWEEN '2024-03-01' AND '2024-03-28'
        AND sales_type != 2  -- 返品
        AND updated_no IS NULL  -- 赤伝
    GROUP BY strftime('%w', sales_date)
),
AggregatedData AS (
    SELECT
        w.week_name,
        w.weekday,
        w.days_count,
        COALESCE(SUM(f.cnt), 0) AS total_cnt,
        COALESCE(SUM(f.total_amt), 0) AS total_amt
    FROM Weekdays w
    LEFT JOIN FilteredData f ON w.weekday = f.weekday
    GROUP BY w.week_name, w.weekday, w.days_count
),
Base AS (
SELECT
    weekday,
    week_name AS WEEK,
    ROUND(CAST(total_cnt AS REAL) / days_count, 0) AS AVG_CNT,
    CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AVG_AMT
FROM AggregatedData
)
SELECT
    WEEK,
    AVG_CNT,
    CASE
        WHEN LENGTH(AVG_AMT) > 6 THEN
            SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT) - 6) || ',' ||
            SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 5, 3) || ',' ||
            SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 2, 3) || '円'
        WHEN LENGTH(AVG_AMT) > 3 THEN
            SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT) - 3) || ',' ||
            SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 2, 3) || '円'
        ELSE
            AVG_AMT || '円'
    END AS AVG_AMT
FROM Base
ORDER BY weekday;