with sales_sum as (
    select 
        strftime('%w',sales_date) as week_no
        , substr('日月火水木金土',strftime('%w',sales_date)+1,1) as week_name
        , round(cast(count(sales_no) as real)/4) as avg_cnt
        , cast(round(cast(sum(sales_amt) as real)/4) as int) as avg_sa_amt
    from sales
    where 
        sales_date between '2024-03-01' and '2024-03-28'
        and sales_type<>2
        and updated_no is null
    group by week_name 
),
week_tbl as (
    select 
        '2024-03-01' as cldate
        , strftime('%w','2024-03-01') as week_no
        , substr('日月火水木金土',strftime('%w','2024-03-01')+1,1) as week_name
    union all
    select 
        date(cldate,'+1 days')
        , strftime('%w',date(cldate,'+1 days'))
        , substr('日月火水木金土',strftime('%w',date(cldate,'+1 days'))+1,1)
    from week_tbl
    where cldate<'2024-03-07'
)
select
    week_tbl.week_name as WEEK
    , ifnull(avg_cnt,0) as AVG_CNT
    , case
        when length(avg_sa_amt)>6 then substr(avg_sa_amt,1,length(avg_sa_amt)-6)||','||substr(avg_sa_amt,length(avg_sa_amt)-5,3)
        when length(avg_sa_amt)>3 then substr(avg_sa_amt,1,length(avg_sa_amt)-3)||','||substr(avg_sa_amt,length(avg_sa_amt)-2,3)
        else ifnull(avg_sa_amt,'0') end ||'円' as AVG_AMT 
from week_tbl
left join sales_sum on sales_sum.week_no=week_tbl.week_no
order by week_tbl.week_no asc;