with T as(
    select
        substr(sales_date,9,12)%7 as d
        ,round(count(*)/4.0,0) as avg_cnt
        ,cast(round(sum(sales_amt)/4.0,0) as int) as avg_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 substr(sales_date,9,12)%7
)
,week as(
    select
        '日' as week,3 as d,1 as sn
    union
    select
        '月' as week,4 as d,2 as sn
    union
    select
        '火' as week,5 as d,3 as sn
    union
    select
        '水' as week,6 as d,4 as sn
    union
    select
        '木' as week,0 as d,5 as sn
    union
    select
        '金' as week,1 as d,6 as sn
    union
    select
        '土' as week,2 as d,7 as sn
)
select
    week as WEEK
    ,ifnull(avg_cnt,0) as AVG_CNT
    ,case 
        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
            ifnull(avg_amt,'0円')
    end as AVG_AMT
from week
left outer join T
on week.d=T.d
order by sn