with summary as (
select
  sales_date,
  count(1) as cnt,
  sum(SALES_AMT) as SUM_AMT
from sales
where SALES_TYPE != 2 and UPDATED_NO IS NULL and sales_date >= '2024-03-01' and sales_date <= '2024-03-28'
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(avg(cnt)) as AVG_CNT
, CAST(ROUND(avg(SUM_AMT)) AS TEXT) 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
;