with recursive agg_per_day as (
    select
        cast(strftime('%w', sales_date) as integer) day_of_week,
        count(sales_no) / 4.0 avg_cnt,
        sum(sales_amt) / 4.0 avg_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 day_of_week
),
generate_series(x) as (
    select 0
    union all
    select x + 1
    from generate_series
    limit 7
)
select
    substr('日月火水木金土', x + 1, 1) WEEK,
    ifnull(round(avg_cnt), 0) AVG_CNT,
    printf('%,d', ifnull(round(avg_amt), 0)) || '円' AVG_AMT
from generate_series gs
left join agg_per_day apd
on gs.x = apd.day_of_week
order by x asc