with a as(
select 
SALES_DATE dt,
cast((strftime('%w',SALES_DATE)) as integer) wk,
sales_amt

 from sales 
where SALES_DATE between '2024-03-01'  and '2024-03-28' 
and SALES_TYPE <> 2 and 
UPDATED_NO is null
)
,
SEQ AS (SELECT 0 as no UNION ALL SELECT no + 1 as no FROM SEQ WHERE no + 1 < 7)

select
WEEK,
amt as 'AVG_CNT',
 case
  when  length(amt2) > 6
     then substr(amt2,1,length(amt2)-6) || ',' || substr(amt2,length(amt2)-5,3) || ',' || substr(amt2,length(amt2)-2,3)
  when  length(amt2) > 3
     then substr(amt2,1,length(amt2)-3) || ',' || substr(amt2,length(amt2)-2,3)
  else  amt2
 end  || '円' AS 'AVG_AMT'

from
(
select 
seq.no no,
substr('日月火水木金土日',seq.no+1,1) as WEEK,
ifnull(cast(round(cast(count(a.dt) as real)/4.0,0) as integer),0) amt,
ifnull(cast(round(cast(sum(SALES_AMT) as real)/4.0,0) as integer),0) amt2
 from seq left join a
 on seq.no=a.wk


group by seq.no
)
order by no