with mst_digit as (
    select 0 as digit
    union
    select 1 as digit
    union 
    select 2 as digit
    union 
    select 3 as digit
    union 
    select 4 as digit
    union 
    select 5 as digit
    union 
    select 6 as digit
    union 
    select 7 as digit
    union 
    select 8 as digit
    union 
    select 9 as digit   
)
, target as (
    SELECT
        (d1.digit + (d2.digit * 10)) AS Number
        ,date('2024-03-' || SUBSTR('00' || cast((d1.digit + (d2.digit * 10)) as text),-2,2)) as target_date
    FROM (mst_digit d1 join mst_digit d2)
    where Number>=1 and Number<=28
    order by Number
)
,tbl_a as(
    select * from target
    left join(
    select * from SALES
    where 
        SALES_DATE between '2024-03-01' and '2024-03-28'
        and SALES_TYPE != 2
        and UPDATED_NO is null)
    on target.target_date = SALES_DATE
)
, tbl_b as (
    select
        target_date as SALES_DATE
        ,case when SALES_AMT is null then 0 else count(*) end as cnt
        ,sum(ifnull(SALES_AMT,0)) as amt
    from tbl_a
    group by
        target_date
)
,tbl_c as (
    select 
        ifnull(strftime("%w", SALES_DATE, 'localtime'),0) as wk
        ,substr('日月火水木金土', strftime("%w", SALES_DATE, 'localtime')+1, 1) as WEEK
        ,round(avg(cnt),0) as AVG_CNT
        ,round(avg(amt),0) as AVG_AMTa
    from tbl_b
    group by WEEK
)
select 
    WEEK
    ,AVG_CNT
    ,case
      when  length(AVG_AMTa) > 8
         then substr(AVG_AMTa,1,length(AVG_AMTa)-8) || ',' || substr(AVG_AMTa,length(AVG_AMTa)-7,3) || ',' || substr(AVG_AMTa,length(AVG_AMTa)-4,3)
      when  length(AVG_AMTa) > 5
         then substr(AVG_AMTa,1,length(AVG_AMTa)-5) || ',' || substr(AVG_AMTa,length(AVG_AMTa)-4,3)
      when  length(AVG_AMTa) > 2
         then substr(AVG_AMTa,1,length(AVG_AMTa)-2) 
      else  substr(AVG_AMTa,1,3)
     end || '円' AS AVG_AMT
from tbl_c
order by wk