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
, length(AVG_AMT)
, 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;