with weekdays as (
select '0' as WEEK_NO, '日' as WEEK union all
select '1', '月' union all
select '2', '火' union all
select '3', '水' union all
select '4', '木' union all
select '5', '金' union all
select '6', '土'
)
, summary as(select sales.sales_date, strftime('%w', sales_date) as WEEK_NO, count(1) as CNT, sum(sales.sales_amt) as SUM_AMT
from sales
where sales.sales_type != 2 and sales.updated_no is null and sales.sales_date between '2024-03-01' and '2024-03-28'
group by sales.sales_date
)
, weekday_summary as (select WEEK_NO, ROUND((1.0 * sum(CNT) / 4)) as AVG_CNT, ROUND((1.0 * sum(SUM_AMT) / 4)) as AVG_AMT
from summary
group by WEEK_NO
)
select 
weekdays.WEEK as WEEK,
ifnull(weekday_summary.AVG_CNT, 0) as AVG_CNT,
case
when weekday_summary.AVG_AMT is null then '0'
else printf("%,d", weekday_summary.AVG_AMT)
end || '円' as AVG_AMT
from weekdays left join weekday_summary on weekdays.WEEK_NO = weekday_summary.WEEK_NO
order by weekdays.WEEK_NO