WITH RECURSIVE calendar(calendar_date) AS (
    SELECT
        -- ここが始まりの日付
        DATE('2024-03-01') AS calendar_date
    UNION ALL
    -- UNION ALL 以下が再帰処理部分
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        -- ここが終わりの日付
        calendar_date < DATE('2024-03-28')
)
,TMP as (
select
    strftime('%w', c.calendar_date) as WEEK
    ,c.calendar_date
    ,coalesce( count(SALES_AMT),0 ) as CNT
    ,coalesce( sum(SALES_AMT),0 ) as AMT
from
    calendar c
    left outer join 
        (select * from SALES where
            SALES_TYPE <> '2'
            and UPDATED_NO is null) s
        on
            c.calendar_date = s.SALES_DATE
group by 1,2
)
,TMP2 as (
select
    WEEK
    ,round(avg(CNT)) as AVG_CNT
    ,replace(cast( round(avg(AMT)) as varchar), '.0', '') as AVG_AMT
from 
    TMP
group by 1
order by WEEK
)
,TMP3 as (
select '0' as WEEK, '日' as D union all
select '1' as WEEK, '月' as D union all
select '2' as WEEK, '火' as D union all
select '3' as WEEK, '水' as D union all
select '4' as WEEK, '木' as D union all
select '5' as WEEK, '金' as D union all
select '6' as WEEK, '土' as D
)
select
    P.D as WEEK
    ,AVG_CNT
    ,case
when length(AVG_AMT)=9 then substr(AVG_AMT, 1,3)||','||substr(AVG_AMT, 4,3)||','||substr(AVG_AMT, 7,3)||'円'
when length(AVG_AMT)=8 then substr(AVG_AMT, 1,2)||','||substr(AVG_AMT, 3,3)||','||substr(AVG_AMT, 6,3)||'円'
when length(AVG_AMT)=7 then substr(AVG_AMT, 1,1)||','||substr(AVG_AMT, 2,3)||','||substr(AVG_AMT, 5,3)||'円'
when length(AVG_AMT)=6 then substr(AVG_AMT, 1,3)||','||substr(AVG_AMT, 4,3)||'円'
when length(AVG_AMT)=5 then substr(AVG_AMT, 1,2)||','||substr(AVG_AMT, 3,3)||'円'
when length(AVG_AMT)=4 then substr(AVG_AMT, 1,1)||','||substr(AVG_AMT, 2,3)||'円'
else AVG_AMT || '円' end as AVG_AMT
from
    TMP3 P
    left outer join
        TMP2 S
        on P.WEEK= S.WEEK
order by P.WEEK;