with recursive calendar(calendar_date) as (
     select date('2024-03-01') as calendar_date
     union all
     select date(calendar_date,'+1 day') as calendar_date
     from calendar
     where calendar_date < date('2024-03-28')
    
    )
    ,tmp1 as (
    select 
    sales_date ,
    count(sales_no) CNT,
    sum(SALES_AMT) AMT
    from SALES
   
    where
    SALES_TYPE!=2 
    AND UPDATED_NO is NULL 
    group by sales_date

    ),
    tmp11 as(
    select calendar_date,
    CASE WHEN CNT is null then 0 else CNT END CNT,
    CASE WHEN AMT is null then 0 else AMT END AMT
    
    from calendar left join tmp1
    on calendar_date=sales_date
    
    )
    ,
    tmp2 as(SELECT 
     strftime("%w", calendar_date) +1 as WEEKD,
     round(AVG(CNT),0) AVG_CNT,
     cast(cast( round(AVG(AMT),0) as integer) as text) AVG_AMT
    FROM tmp11
    group by 1
    ),tmp3 as (select 1 nn union select 2 nn 
    union select 3 nn union 
    select 4 nn union select 5 nn union select 6 nn union select 7 nn)
 
   SELECT
    substr('日月火水木金土',nn,1) WEEK,
    CASE WHEN 
    AVG_AMT is null then '0円'
    WHEN length(AVG_AMT)>6 then 
    substr(AVG_AMT,1,length(AVG_AMT)-6) || ',' || substr(AVG_AMT,length(AVG_AMT)-5,3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)||'円'
    WHEN length(AVG_AMT)>3 then 
    substr(AVG_AMT,1,length(AVG_AMT)-3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)||'円'
    ELSE
    AVG_AMT || '円'
     END AVG_AMT,
    CASE WHEN
    AVG_CNT is null then '0'
    ELSE AVG_CNT END AVG_CNT
    FROM 
    tmp3 left join tmp2 on tmp3.nn=tmp2.weekd
    order by nn;