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
)

SELECT
    week_name AS WEEK,
    ROUND(CAST(total_cnt AS REAL) / days_count, 0) AS AVG_CNT,
    CASE
        WHEN LENGTH(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT)) > 6 THEN
            SUBSTR(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT), 1, LENGTH(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT)) - 6) || ',' ||
            SUBSTR(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT), LENGTH(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT)) - 5, 3) || ',' ||
            SUBSTR(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT), LENGTH(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT)) - 2, 3) || '円'
        WHEN LENGTH(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT)) > 3 THEN
            SUBSTR(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT), 1, LENGTH(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT)) - 3) || ',' ||
            SUBSTR(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT), LENGTH(CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT)) - 2, 3) || '円'
        ELSE
            CAST(CAST(ROUND(CAST(total_amt AS REAL) / days_count, 0) AS INTEGER) AS TEXT) || '円'
    END AS AVG_AMT
FROM AggregatedData
ORDER BY weekday;