WITH filtered_sales AS (
    SELECT
        SALES_NO,
        SALES_DATE,
        SALES_AMT,
        strftime('%w', SALES_DATE) AS WEEKDAY
    FROM
        SALES
    WHERE
        SALES_DATE BETWEEN '2024-03-01' AND '2024-03-28'
        AND SALES_TYPE != 2
        AND UPDATED_NO IS NULL
),
sales_by_day AS (
    SELECT
        WEEKDAY,
        COUNT(*) AS DAILY_COUNT,
        SUM(SALES_AMT) AS DAILY_AMT
    FROM
        filtered_sales
    GROUP BY
        WEEKDAY
),
days_in_range AS (
    SELECT 0 AS WEEKDAY UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
),
sales_by_weekday AS (
    SELECT
        d.WEEKDAY,
        COALESCE(s.DAILY_COUNT, 0) AS DAILY_COUNT,
        COALESCE(s.DAILY_AMT, 0) AS DAILY_AMT,
        CASE
            WHEN d.WEEKDAY = 0 THEN '日'
            WHEN d.WEEKDAY = 1 THEN '月'
            WHEN d.WEEKDAY = 2 THEN '火'
            WHEN d.WEEKDAY = 3 THEN '水'
            WHEN d.WEEKDAY = 4 THEN '木'
            WHEN d.WEEKDAY = 5 THEN '金'
            WHEN d.WEEKDAY = 6 THEN '土'
        END AS WEEK
    FROM
        days_in_range d
        LEFT JOIN sales_by_day s ON d.WEEKDAY = s.WEEKDAY
)
SELECT
    WEEK,
    ROUND(SUM(DAILY_COUNT) / 4.0, 0) AS AVG_CNT,
    ROUND(SUM(DAILY_AMT) / 4.0, 0) || '円' AS AVG_AMT
FROM
    sales_by_weekday
GROUP BY
    WEEKDAY
ORDER BY
    WEEKDAY;