WITH RECURSIVE 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')
),
-- select * from calendar order by 1;
target as (
    select
        calendar.calendar_date,
        strftime('%w', date(calendar.calendar_date)) as week_temp,
        count(sales.sales_no) as sales_count,
        sum(ifnull(sales_amt,0)) as sales_amt
    from calendar 
        left outer join sales
            on calendar.calendar_date = sales.sales_date
                and sales.sales_type <> 2
                and sales.updated_no is null
                and sales.sales_date between '2024-03-01' and '2024-03-28'
    group by 1,2
), temp as (
    select
        case
            when week_temp = '0' then '日'
            when week_temp = '1' then '月'
            when week_temp = '2' then '火'
            when week_temp = '3' then '水'
            when week_temp = '4' then '木'
            when week_temp = '5' then '金'
            when week_temp = '6' then '土'
            else null
        end as WEEK,
        week_temp,
        round(avg(sales_count),0) as AVG_CNT,
        cast(round(avg(sales_amt),0) * 1 as string) as AVG_AMT
    from target
    group by 1,2
    order by week_temp
), 
temp2 as (
    select *,
        case
            when length(AVG_AMT) > 2 
                then substr(AVG_AMT, 1, length(AVG_AMT)-2)
            else AVG_AMT
        end as AVG_AMT2
    from temp
)
-- select *, length(avg_amt) from temp2 ;
select 
    WEEK,
    AVG_CNT,
    case
        when length(AVG_AMT2) > 6
            then substr(AVG_AMT2,1,length(AVG_AMT2)-6) || 
                ',' || substr(AVG_AMT2,length(AVG_AMT2)-5,3) || 
                ',' || substr(AVG_AMT2,length(AVG_AMT2)-2,3) || '円'
        when  length(AVG_AMT2) > 3
            then substr(AVG_AMT2,1,length(AVG_AMT2)-3) || 
                ',' || substr(AVG_AMT2,length(AVG_AMT2)-2,3) || '円'
        else  '0円'
    end as AVG_AMT
from temp2
order by week_temp