with youbiTable as
(
select
strftime('%w', sales_date) as youbi_no
,*
from
sales
where
sales_type <> 2 and updated_no is null and sales_date between '2024-03-01' and '2024-03-28'
)

,calendar(calendar_date) AS (
    SELECT
        -- ここが始まりの日付
        DATE('2024-03-01') AS calendar_date
    UNION ALL
    -- UNION ALL 以下が再帰処理部分
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        -- ここが終わりの日付
        calendar_date < DATE('2024-03-28')
)

,youbiCountTable as
(
select
youbi_no,
count(youbi_no) as count_youbi
from
(
select
calendar_date,
strftime('%w', calendar_date) as youbi_no
from
calendar
)
group by youbi_no 
)

,answer as (
select
    case when yct.youbi_no = '0' then '日' when yct.youbi_no = '1' then '月' when yct.youbi_no = '2' then '火' when yct.youbi_no = '3' then '水' when yct.youbi_no = '4' then '木' when yct.youbi_no = '5' then '金' when yct.youbi_no = '6' then '土' end as WEEK
    , round(cast(count(*) as real) / cast(count_youbi as real), 0) as AVG_CNT
    , case when sales_amt is null then 0 else cast(round(cast(sum(sales_amt) as real) / cast(count_youbi as real), 0) as integer) end as AVG_AMT
from
    youbiTable as yt
full outer join
    youbiCountTable as yct
on
    yt.youbi_no = yct.youbi_no
group by yt.youbi_no, yct.count_youbi
order by yct.youbi_no
)

select
    WEEK,
    AVG_CNT,
     case
        when  length(AVG_AMT) > 6
            then substr(AVG_AMT,1,length(AVG_AMT)-6) || ',' || substr(AVG_AMT,length(AVG_AMT)-5,3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3) || '円'
        when  length(AVG_AMT) > 3
            then substr(AVG_AMT,1,length(AVG_AMT)-3) || ',' || substr(AVG_AMT,length(AVG_AMT)-2,3) || '円'
     else  AVG_AMT || '円' END as AVG_AMT
from
    answer