with cte as (
select
    SALES_DATE	
    ,count() as CNT
    ,sum(SALES_AMT) as AMT
from
    SALES
where
    SALES_DATE between '2024-03-01' and '2024-03-28'
    and SALES_TYPE!=2 and UPDATED_NO is null
group by
    SALES_DATE
)
,cte2 as (
select
    cast(strftime('%w', SALES_DATE) as int) as W_CD
    , cast(round(1.0*sum(CNT)/4,0) as int) as AVG_CNT
    , cast(round(1.0*sum(AMT)/4,0) as int) as AVG_AMT
from
    cte
group by
    W_CD
)
,base as (
select 0 as W_CD ,"日" as W
union
select 1 as W_CD ,"月" as W
union
select 2 as W_CD ,"火" as W
union
select 3 as W_CD ,"水" as W
union
select 4 as W_CD ,"木" as W
union
select 5 as W_CD ,"金" as W
union
select 6 as W_CD ,"土" as W

)
,cte3 as (
select
    b.W_CD
    ,b.W
    ,coalesce(a.AVG_CNT,0) as AVG_CNT
    ,cast(coalesce(a.AVG_AMT,0) as char) as AVG_AMT
from
    base as b
    left join
    cte2 as a
    on b.W_CD = a.W_CD
)
select
    W as WEEK
    ,AVG_CNT
  ,CASE
    WHEN LENGTH(AVG_AMT) <= 3 THEN AVG_AMT || '円'
    WHEN LENGTH(AVG_AMT) <= 6 THEN SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT) - 3) || ',' || SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 2) || '円'
    ELSE SUBSTR(AVG_AMT, 1, LENGTH(AVG_AMT) - 6) || ',' || SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 5, 3) || ',' || SUBSTR(AVG_AMT, LENGTH(AVG_AMT) - 2) || '円'
  END AS AVG_AMT
from
    cte3
order by
    W_CD