with st as (select * from sales
where sales_date between '2024-03-01' and '2024-03-28' and sales_type != 2 and updated_no is null
)
, weeks as (
select 0 as wn
union all 
select wn + 1
from weeks
where wn < 6
)
, daily as (
select sales_date, count(1) as cnt, sum(sales_amt) as amt
from st
group by sales_date
)
-- select * from weeks
, weekly as (
select cast(strftime('%w', sales_date) as integer) as wn, 1.0 * sum(cnt) / 4 as avg_cnt, cast(ROUND(1.0 * sum(amt) / 4) as integer) as avg_amt
from daily
group by strftime('%w', sales_date)
)
select 
substr('日月火水木金土', weeks.wn + 1, 1) as WEEK
, ifnull(ROUND(weekly.avg_cnt), 0) as AVG_CNT
, ifnull(printf('%,d', weekly.avg_amt), 0) || '円' as AVG_AMT
from weeks
left join weekly on weeks.wn = weekly.wn
order by weeks.wn