with tmp1 as (
    select 
        sales_date,
        strftime('%w',date(sales_date)) as weekno,
        substr('日月火水木金土',strftime('%w',sales_date)+1,1) as WEEK,
        sales_amt,
        sales_type,
        updated_no,
        1 as countnum
    from sales
    where sales_date between '2024-03-01' and '2024-03-28' 
    and sales_type<>2 
    and updated_no is null
),
tmp2 as (
    select
        weekno,
        WEEK,
        sum(sales_amt) as sumamt,
        count(sales_date) as weekcount
    from tmp1
    group by week
),
tmp3 as (
    select 
        cast(row_number() over(order by sales_date)-1 as string) as no,
        substr('日月火水木金土',row_number() over(order by sales_date),1) as WEEK_
    from sales
    )
select 
    WEEK_,
    ifnull(round(1.0*weekcount/4,0),0) as AVG_CNT,
    printf('%,d円',round(1.0*sumamt/4,0)) as AVG_AMT
from tmp2
right outer join (
    select * from tmp3
    where no between 0 and 6)
on weekno=no
order by no;