with tmp 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
  ,sum(case when sales_type != 2 and updated_no is null then 1 else 0 end) as order_cnt 
  ,sum(case when sales_type != 2 and updated_no is null then sales_amt else 0 end) as total_yen
  ,count(distinct sales_date) as day_count
from sales
where sales_date >= '2024-03-01' 
  and sales_date <= '2024-03-28'
group by strftime('%w', sales_date)
order by strftime('%w', sales_date)
)
, tmp_2 as (
select 
  week as WEEK
  ,case when day_count = 0 then 0
    else round(order_cnt * 1.0 / day_count, 0) 
  end as AVG_CNT
  ,case when day_count = 0 then '0'
    else cast(round(total_yen * 1.0 / day_count, 0) as integer)
  end as AVG_AMT
from tmp 
)
, tmp_3 as (
select 
  WEEK
  ,AVG_CNT
  ,case when length(AVG_AMT) > 6 
    then substr(AVG_AMT,1,length(AVG_AMT)-6) || ',' || substr(AVG_AMT,length(AVG_AMT)-5,3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)
    when length(AVG_AMT) > 3
    then substr(AVG_AMT,1,length(AVG_AMT)-3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3)
  else AVG_AMT
  end as AVG_AMT
from tmp_2
)
select 
  WEEK
  ,AVG_CNT
  ,AVG_AMT||'円' as AVG_AMT
from tmp_3
order by 
  CASE WEEK
      WHEN '日' THEN 1
      WHEN '月' THEN 2
      WHEN '火' THEN 3
      WHEN '水' THEN 4
      WHEN '木' THEN 5
      WHEN '金' THEN 6
      WHEN '土' THEN 7
  END
;