WITH S1 AS (
    SELECT
        *
    FROM
        SALES
    WHERE
        SALES_DATE >= '2024-03-01' AND SALES_DATE <= '2024-03-28'
        AND SALES_TYPE <> 2 AND UPDATED_NO IS NULL
),
S2 AS (
    SELECT
        strftime('%w', SALES_DATE) AS WEEK,
        ROUND(COUNT(strftime('%w', SALES_DATE)) / 4.0) AS COUNT,
        CAST(ROUND(SUM(SALES_AMT) / 4.0) AS INTEGER) AS TOTAL
    FROM
        S1
    GROUP BY
        WEEK
),
W1 as (
    SELECT '0' WEEK
    UNION SELECT '1' WEEK
    UNION SELECT '2' WEEK
    UNION SELECT '3' WEEK
    UNION SELECT '4' WEEK
    UNION SELECT '5' WEEK
    UNION SELECT '6' WEEK
)
SELECT
    CASE W1.WEEK
        WHEN '0' THEN '日'
        WHEN '1' THEN '月'
        WHEN '2' THEN '火'
        WHEN '3' THEN '水'
        WHEN '4' THEN '木'
        WHEN '5' THEN '金'
        WHEN '6' THEN '土'
    END AS WEEK,
    IFNULL(S2.COUNT, 0) as AVG_CNT,
    CASE
        WHEN S2.TOTAL IS NULL THEN '0' || '円'
        WHEN LENGTH(S2.TOTAL) > 6 THEN
            SUBSTR(S2.TOTAL, 1, LENGTH(S2.TOTAL) - 6) || ',' ||
            SUBSTR(S2.TOTAL, LENGTH(S2.TOTAL) - 5, 3) || ',' ||
            SUBSTR(S2.TOTAL, LENGTH(S2.TOTAL) - 2, 3) || '円'
        WHEN LENGTH(S2.TOTAL) > 3 THEN
            SUBSTR(S2.TOTAL, 1, LENGTH(S2.TOTAL) - 3) || ',' ||
            SUBSTR(S2.TOTAL, LENGTH(S2.TOTAL) - 2, 3) || '円'
        ELSE
            S2.TOTAL || '円'
    END AS AVG_AMT
FROM
    W1
    LEFT JOIN S2 ON W1.WEEK = S2.WEEK 
ORDER BY
    W1.WEEK