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) AS AVG_CNT
        , CAST(ROUND(CAST(SUM(SALES_AMT) AS REAL) / 4) 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_NAME
) 
, 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
    WEEK_TBL.WEEK_NAME AS WEEK
    , IFNULL(AVG_CNT, 0) AS AVG_CNT
    , CASE 
        WHEN LENGTH(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 
    LEFT OUTER JOIN SALES_SUM 
    ON SALES_SUM.WEEK_NO = WEEK_TBL.WEEK_NO 
ORDER BY WEEK_TBL.WEEK_NO ASC;