with 
-- days as (
-- select '2024-03-01' as day union all
-- select '2024-03-02' union all
-- select '2024-03-03' union all
-- select '2024-03-04' union all
-- select '2024-03-05' union all
-- select '2024-03-06' union all
-- select '2024-03-07' union all
-- select '2024-03-04' as day union all
-- select '2024-03-08' as day union all
-- select '2024-03-09' as day union all
-- select '2024-03-10' as day union all
-- select '2024-03-11' as day union all
-- select '2024-03-12' as day union all
-- select '2024-03-13' as day union all
-- select '2024-03-14' as day union all
-- select '2024-03-15' as day union all
-- select '2024-03-16' as day union all
-- select '2024-03-17' as day union all
-- select '2024-03-18' as day union all
-- select '2024-03-19' as day union all
-- select '2024-03-20' as day union all
-- select '2024-03-21' as day union all
-- select '2024-03-22' as day union all
-- select '2024-03-23' as day union all
-- select '2024-03-24' as day union all
-- select '2024-03-25' as day union all
-- select '2024-03-26' as day union all
-- select '2024-03-27' as day union all
-- select '2024-03-28' as day
-- )
-- ,
summary as (
select sales_date, count(1) as cnt, sum(sales_amt) as SUM_AMT 
from sales
where sales_date between '2024-03-01' and '2024-03-28' and sales_type != 2 and updated_no is null
group by sales_date
)
, weekdays as (
select 0 as week, '日' as weekday UNION ALL
select 1, '月' UNION ALL
select 2, '火' union ALL
select 3, '水' union all
select 4, '木' union all
select 5, '金' union all
select 6, '土'
)
, st as (
select 
CASE strftime('%w', sales_date)
WHEN '0' THEN '日'
WHEN '1' THEN '月'
WHEN '2' THEN '火'
WHEN '3' THEN '水'
WHEN '4' THEN '木'
WHEN '5' THEN '金'
WHEN '6' THEN '土'
END as WEEK
, ROUND(1.0 * sum(cnt) / 4) as AVG_CNT
, CAST(ROUND(1.0 * sum(SUM_AMT) / 4) AS INTEGER) AS AVG_AMT
from summary
group by WEEK
order by strftime('%w', sales_date)
)
select 
weekdays.weekday as WEEK,
case when st.AVG_CNT is null then 0 else st.AVG_CNT end as AVG_CNT,
case when st.AVG_AMT is null then 0 || '円' else 
case
  when  length(st.AVG_AMT) > 6
     then substr(st.AVG_AMT,1,length(st.AVG_AMT)-6) || ',' || substr(st.AVG_AMT,length(st.AVG_AMT)-5,3) || ',' || substr(st.AVG_AMT,length(st.AVG_AMT)-2,3) || '円'
  when  length(st.AVG_AMT) > 3
     then substr(st.AVG_AMT,1,length(st.AVG_AMT)-3) || ',' || substr(st.AVG_AMT,length(st.AVG_AMT)-2,3) || '円'
  else st.AVG_AMT || '円'
end 
end as AVG_AMT
from weekdays
left join st on weekdays.weekday = st.week
order by weekdays.week
;