with w as (
select
0 as d
union all
select d + 1 from w
where d < 6
)
, 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
)
, st2 as (
select
count(1) as CNT
, SUM(SALES_AMT) as SUM_AMT
, sales_date
from st
group by sales_date
)
, st3 as (
select 
cast(strftime('%w', sales_date) as int) as w
, ROUND(1.0 * SUM(CNT) / 4) as AVG_CNT
, ROUND(1.0 * SUM(SUM_AMT) / 4) as AVG_AMT
from st2
group by strftime('%w', sales_date)
)
select 
substr('日月火水木金土', w.d + 1, 1) as WEEK
, ifnull(st3.AVG_CNT, 0) as AVG_CNT
, printf("%,d", ifnull(st3.AVG_AMT, 0)) || '円' as AVG_AMT
from w left join st3 on w.d = st3.w
order by w.d