with recursive agg_per_day as (
    select
        sales_date,
        cast(strftime('%w', sales_date) as integer) day_of_week,
        count(sales_no) cnt,
        sum(sales_amt) total_amt
    from sales
    where
        sales_type != 2
        and updated_no is null
        and sales_date between '2024-03-01' and '2024-03-28'
    group by sales_date
),
generate_series(x) as (
    select 0
    union all
    select x + 1
    from generate_series
    limit 7
)
select
    case x
    when 0 then '日'
    when 1 then '月'
    when 2 then '火'
    when 3 then '水'
    when 4 then '木'
    when 5 then '金'
    when 6 then '土'
    end WEEK,
    ifnull(round(sum(cnt) / 4.0), 0) AVG_CNT,
    printf('%,d', round(sum(total_amt) / 4.0)) || '円' AVG_AMT
from generate_series gs
left join agg_per_day apd
on gs.x = apd.day_of_week
group by day_of_week
order by x asc