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,
    CASE
        WHEN S2.COUNT IS NOT NULL THEN S2.COUNT
        ELSE '0'
    END AS AVG_CNT,
    CASE 
        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
            CASE
                WHEN S2.TOTAL IS NOT NULL THEN S2.TOTAL || '円'
                ELSE '0' || '円'
            END
    END AS AVG_AMT
FROM
    W1
    LEFT JOIN S2 ON W1.WEEK = S2.WEEK 
ORDER BY
    S2.WEEK