WITH WEEKS(WEEK_STR, WEEK_JP) AS
    (
        values
        ('0', '日')
        ,('1', '月')
        ,('2', '火')
        ,('3', '水')
        ,('4', '木')
        ,('5', '金')
        ,('6', '土')
    )
    ,EVERYDAY(DAY) AS 
    (
        values
        ('2024-03-01')
        ,('2024-03-02')
        ,('2024-03-03')
        ,('2024-03-04')
        ,('2024-03-05')
        ,('2024-03-06')
        ,('2024-03-07')
        ,('2024-03-08')
        ,('2024-03-09')
        ,('2024-03-10')
        ,('2024-03-11')
        ,('2024-03-12')
        ,('2024-03-13')
        ,('2024-03-14')
        ,('2024-03-15')
        ,('2024-03-16')
        ,('2024-03-17')
        ,('2024-03-18')
        ,('2024-03-19')
        ,('2024-03-20')
        ,('2024-03-21')
        ,('2024-03-22')
        ,('2024-03-23')
        ,('2024-03-24')
        ,('2024-03-25')
        ,('2024-03-26')
        ,('2024-03-27')
        ,('2024-03-28')
    )
    ,SALES_QUERY(SALES_DATE, CNT, AMT) AS
    (
        SELECT
        SALES_DATE 
        , SUM(1) AS CNT
        , SUM(SALES_AMT) AS AMT
        FROM SALES
        WHERE 
        UPDATED_NO IS NULL
        AND (SALES_TYPE IS NULL OR SALES_TYPE <> 2)
        AND SALES_DATE BETWEEN "2024-03-01" AND "2024-03-28"
        GROUP BY SALES_DATE
    )
    , ANS(WEEK, AVG_CNT, AVG_AMT) AS(
        SELECT 
        W.WEEK_JP AS WEEK
        ,ROUND(AVG(coalesce(S.CNT, 0))) AS AVG_CNT
        ,cast(cast(ROUND(AVG(coalesce(S.AMT, 0))) as integer) as text) AS AVG_AMT
        FROM 
            WEEKS AS W 
            JOIN EVERYDAY AS E ON W.WEEK_STR = strftime('%w', E.DAY)
            LEFT OUTER JOIN SALES_QUERY AS S ON (E.DAY = S.SALES_DATE)
        GROUP BY W.WEEK_JP
        ORDER BY W.WEEK_STR
    )
    SELECT
    WEEK
    , AVG_CNT
    , case 
        when length(AVG_AMT) <= 3
            then AVG_AMT
        when length(AVG_AMT) <= 6
            then substr(AVG_AMT, max(1,length(AVG_AMT)-5), length(AVG_AMT)-3) || ',' || substr(AVG_AMT, length(AVG_AMT)-2, 3)
        when length(AVG_AMT) <= 9
            then substr(AVG_AMT, max(1,length(AVG_AMT)-8), length(AVG_AMT)-6) || ',' || substr(AVG_AMT, length(AVG_AMT)-5, 3) || ',' || substr(AVG_AMT, length(AVG_AMT)-2, 3)
    end || '円' AS AVG_AMT
    FROM ANS