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 
)


select
    case when yt.youbi_no is null then '日' when yt.youbi_no = '1' then '月' when yt.youbi_no = '2' then '火' when yt.youbi_no = '3' then '水' when yt.youbi_no = '4' then '木' when yt.youbi_no = '5' then '金' when yt.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 round(cast(sum(sales_amt) as real) / cast(count_youbi as real)) 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