with cal as (
    select date('2024-03-01') as date, strftime('%w', '2024-03-01') as yobi union all
    select date('2024-03-02'), strftime('%w', '2024-03-02') union all
    select date('2024-03-03'), strftime('%w', '2024-03-03') union all
    select date('2024-03-04'), strftime('%w', '2024-03-04') union all
    select date('2024-03-05'), strftime('%w', '2024-03-05') union all
    select date('2024-03-06'), strftime('%w', '2024-03-06') union all
    select date('2024-03-07'), strftime('%w', '2024-03-07') union all
    select date('2024-03-08'), strftime('%w', '2024-03-08') union all
    select date('2024-03-09'), strftime('%w', '2024-03-09') union all
    select date('2024-03-10'), strftime('%w', '2024-03-10') union all
    select date('2024-03-11'), strftime('%w', '2024-03-11') union all
    select date('2024-03-12'), strftime('%w', '2024-03-12') union all
    select date('2024-03-13'), strftime('%w', '2024-03-13') union all
    select date('2024-03-14'), strftime('%w', '2024-03-14') union all
    select date('2024-03-15'), strftime('%w', '2024-03-15') union all
    select date('2024-03-16'), strftime('%w', '2024-03-16') union all
    select date('2024-03-17'), strftime('%w', '2024-03-17') union all
    select date('2024-03-18'), strftime('%w', '2024-03-18') union all
    select date('2024-03-19'), strftime('%w', '2024-03-19') union all
    select date('2024-03-20'), strftime('%w', '2024-03-20') union all
    select date('2024-03-21'), strftime('%w', '2024-03-21') union all
    select date('2024-03-22'), strftime('%w', '2024-03-22') union all
    select date('2024-03-23'), strftime('%w', '2024-03-23') union all
    select date('2024-03-24'), strftime('%w', '2024-03-24') union all
    select date('2024-03-25'), strftime('%w', '2024-03-25') union all
    select date('2024-03-26'), strftime('%w', '2024-03-26') union all
    select date('2024-03-27'), strftime('%w', '2024-03-27') union all
    select date('2024-03-28'), strftime('%w', '2024-03-28')
)
,sales1 as(
select *
from SALES
where SALES_TYPE <> 2 
and UPDATED_NO is null
)
,sales2 as (
select *
from cal
    left join sales1
     on cal.date = sales1.SALES_DATE
)
,sales3 as (
select
    date, yobi,
    count(SALES_NO) as sal_cnt,
    ifnull(sum(SALES_AMT), 0) as sal_avg
from sales2
group by date
)
select
    case yobi
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
        else 'その他'
    end as WEEK
    ,round(avg(sal_cnt), 0) as AVG_CNT
    --,cast(round(avg(sal_avg), 0) as integer)
    ,CASE
        WHEN LENGTH(cast(round(avg(sal_avg), 0) as integer)) > 6 THEN
            SUBSTR(cast(round(avg(sal_avg), 0) as integer), 1, LENGTH(cast(round(avg(sal_avg), 0) as integer)) - 6) || ',' || SUBSTR(cast(round(avg(sal_avg), 0) as integer), LENGTH(cast(round(avg(sal_avg), 0) as integer)) - 5, 3) || ',' || SUBSTR(cast(round(avg(sal_avg), 0) as integer), LENGTH(cast(round(avg(sal_avg), 0) as integer)) - 2, 3)
        WHEN LENGTH(cast(round(avg(sal_avg), 0) as integer)) > 3 THEN
            SUBSTR(cast(round(avg(sal_avg), 0) as integer), 1, LENGTH(cast(round(avg(sal_avg), 0) as integer)) - 3) || ',' || SUBSTR(cast(round(avg(sal_avg), 0) as integer), LENGTH(cast(round(avg(sal_avg), 0) as integer)) - 2, 3)
        ELSE
            cast(round(avg(sal_avg), 0) as integer)
    END || '円'as AVG_AMT
from sales3
group by yobi
order by yobi