WITH
    SALES_SUM AS (
        SELECT
            STRFTIME ('%w', SALES_DATE) AS WEEK_NO,
            SUBSTR ('日月火水木金土', STRFTIME ('%w', SALES_DATE) + 1, 1) AS WEEK_NAME,
            ROUND(CAST(COUNT(SALES_NO) AS REAL) / 4.0) AS AVG_CNT,
            CAST(ROUND(CAST(SUM(SALES_AMT) AS REAL) / 4.0) AS INT) AS AVG_SA_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
            WEEK_NO
    ),
    WEEK_TBL AS (
        SELECT
            '2024-03-01' AS CLDATE,
            STRFTIME ('%w', '2024-03-01') AS WEEK_NO,
            SUBSTR ('日月火水木金土', STRFTIME ('%w', '2024-03-01') + 1, 1) AS WEEK_NAME
        UNION ALL
        SELECT
            DATE(CLDATE, '+1 days'),
            STRFTIME ('%w', DATE(CLDATE, '+1 days')),
            SUBSTR (
                '日月火水木金土',
                STRFTIME ('%w', DATE(CLDATE, '+1 days')) + 1,
                1
            )
        FROM
            WEEK_TBL
        WHERE
            CLDATE < '2024-03-07'
    )
SELECT
    wt.WEEK_NAME AS WEEK,
    COALESCE(ss.AVG_CNT, 0) AS AVG_CNT,
    CASE
        WHEN LENGTH (ss.AVG_SA_AMT) > 6 THEN SUBSTR (AVG_SA_AMT, 1, LENGTH (AVG_SA_AMT) - 6) || ',' || SUBSTR (AVG_SA_AMT, LENGTH (AVG_SA_AMT) - 5, 3) || ',' || SUBSTR (AVG_SA_AMT, LENGTH (AVG_SA_AMT) - 2, 3)
        WHEN LENGTH (AVG_SA_AMT) > 3 THEN SUBSTR (AVG_SA_AMT, 1, LENGTH (AVG_SA_AMT) - 3) || ',' || SUBSTR (AVG_SA_AMT, LENGTH (AVG_SA_AMT) - 2, 3)
        ELSE IFNULL (AVG_SA_AMT, '0')
    END || '円' AS AVG_AMT
FROM
    WEEK_TBL AS wt
    LEFT OUTER JOIN SALES_SUM AS ss ON ss.WEEK_NO = wt.WEEK_NO
ORDER BY
    wt.WEEK_NO ASC;