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',
(
WITH SEQ AS (SELECT amt2 a, null b,0 as no UNION ALL 
SELECT substr(a,1,length(a)-3), substr(a,-3) b,no+1 FROM SEQ WHERE length(a)>=1)
select group_concat(b)  from (select * from seq order by no desc) where b is not null
) || '円' 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