with daily_sales as(
select SALES_DATE,
count(*) as sales_cnt,
sum(SALES_AMT) as daily_sales_amt,
ROW_NUMBER() OVER(ORDER BY SALES_DATE) as rownum
from SALES
where SALES_DATE >= "2024-03-01" and SALES_DATE < "2024-03-29"
group by SALES_DATE
)
select 
case 
when (rownum-1) % 7 =0 then "日"
when (rownum-1)% 7 =1 then "月"
when (rownum-1) % 7 =2 then "火"
when (rownum-1) % 7 =3 then "水"
when (rownum-1) % 7 =4 then "木"
when (rownum-1) % 7 =5 then "金"
when (rownum-1) % 7 =6 then "土"
end as WEEK,
case
when count(1) = 0 then 0
else sum(sales_cnt)/count(1) 
end as AVG_CNT,
case
when count(1) = 0 then "0円"
else printf('%,d円', sum(daily_sales_amt)/count(1)) 
--else printf(,,cast(sum(daily_sales_amt)/count(1) as text) ) || '円' 
end as AVG_AMT
from daily_sales
group by WEEK
order by case WEEK
when "日" then 1
when "月" then 2
when "火" then 3
when "水" then 4
when "木" then 5
when "金" then 6
else 7
end