WITH SALES_SUM AS ( 
   SELECT
        STRFTIME('%w', SALES_DATE) AS WEEK_NO
        , CASE STRFTIME('%w', SALES_DATE) 
            WHEN '0' THEN '日'
            WHEN '1' THEN '月'
            WHEN '2' THEN '火'
            WHEN '3' THEN '水'
            WHEN '4' THEN '木'
            WHEN '5' THEN '金'
            WHEN '6' THEN '土'
          END 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;