with

dummy AS
(
  SELECT 1 AS row_no 
  UNION ALL
  SELECT row_no + 1
  FROM dummy
  WHERE dummy.row_no < 28
),

d1 as (

select 

(case when row_no <= 9 then "2024-03-0" || cast(row_no as string) else "2024-03-" || cast(row_no as string) end) as SALES_DATE
from dummy
),


ta as (select SALES_DATE, SALES_AMT, 1 as SALES_N
from SALES
where "2024-03-01" <= SALES_DATE and SALES_DATE <= "2024-03-28" and
SALES_TYPE != 2 and UPDATED_NO is null
),
tb as (
select SALES_DATE, sum(SALES_N) as SALES_N, sum(SALES_AMT) as SALES_AMT
from ta
group by SALES_DATE
),
tc as (
select SALES_DATE, ifnull(SALES_N,0) as SALES_N, ifnull(SALES_AMT,0) as SALES_AMT , strftime('%w', SALES_DATE) as d
from tb 
full outer join d1 using(SALES_DATE)
order by SALES_DATE
),

td as (
select d, round(avg(SALES_N) + 0.001) as AVG_CNT, 
(case when sum(SALES_N) = 0 then 0 else round(avg(SALES_AMT)+0.001) end) as AVG_AMT
from tc
group by d
),

te as (
select *, cast(AVG_AMT as string) as AVG_S
from td
order by d
),

tf as (
select (case
when d = "0" then "日"
when d = "1" then "月"
when d = "2" then "火"
when d = "3" then "水"
when d = "4" then "木"
when d = "5" then "金"
when d = "6" then "土" end
) as WEEK,
AVG_CNT,
(case
when AVG_AMT >= 1000000 then substr(AVG_S,-11,3) || "," || substr(AVG_S,-8,3) || "," || substr(AVG_S,-5,3) || "円"
when AVG_AMT >= 1000 then substr(AVG_S,-8,3) || "," || substr(AVG_S,-5,3) || "円"
else AVG_S || "円"  end
) as AVG_AMT
from te
)


select * from tf