with
    weeks as (
        select
            '0' as week
        union
        select
            '1' as week
        union
        select
            '2' as week
        union
        select
            '3' as week
        union
        select
            '4' as week
        union
        select
            '5' as week
        union
        select
            '6' as week
    )
    ,data as (
        select
            strftime('%w', sales_date) as week
            ,round(cast(count(sales_no) as real) / 4, 0) as AVG_CNT
            ,sum(sales_amt) / 4 as AVG_AMT
        from
            sales
        where
            sales_type != 2
            and updated_no is null
            and sales_date >= '2024-03-01'
            and sales_date <= '2024-03-28'
        group by
            1
    )
    ,jt as (
        select
            weeks.week
            ,case
                when data.avg_cnt is null then 0
                else data.avg_cnt
                end as avg_cnt
            ,case
                when data.avg_amt is null then 0
                else data.avg_amt
                end as avg_amt
        from
            weeks
        left join
            data
        on
            weeks.week = data.week
    )

select
    case
        when week = '0' then '日'
        when week = '1' then '月'
        when week = '2' then '火'
        when week = '3' then '水'
        when week = '4' then '木'
        when week = '5' then '金'
        when week = '6' then '土'
        end as WEEK
    ,avg_cnt 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  avg_amt || '円'
        end as AVG_AMT
from
    jt
order by
    case
        when week = '日' then 1
        when week = '月' then 2
        when week = '火' then 3
        when week = '水' then 4
        when week = '木' then 5
        when week = '金' then 6
        when week = '土' then 7
        end