with base_date as (
    SELECT
         date('2022-08-01') as REGIST_DATE
        -- ,substr('日月火水木金土', strftime("%w", REGIST_DATE, 'localtime')+1, 1) as weekname
    UNION ALL
    SELECT
        date(REGIST_DATE ,'+1 days') as REGIST_DATE
    FROM
        base_date
    WHERE
        REGIST_DATE < '2022-08-31'
),base_date2 as (
    SELECT
        REGIST_DATE
        ,case
            when strftime('%w',REGIST_DATE) = '0' then '日'
            when strftime('%w',REGIST_DATE) = '1' then '月'
            when strftime('%w',REGIST_DATE) = '2' then '火'
            when strftime('%w',REGIST_DATE) = '3' then '水'
            when strftime('%w',REGIST_DATE) = '4' then '木'
            when strftime('%w',REGIST_DATE) = '5' then '金'
            when strftime('%w',REGIST_DATE) = '6' then '土'
        end as WK
    FROM
        base_date
),data as (
    SELECT
        substring(CONFIRMED_AT,1,10) as d
        ,count(*) as TOTAL
    FROM
        USERS
    WHERE
        VALID_FLG = '1'
    GROUP BY substring(CONFIRMED_AT,1,10)
    HAVING substring(CONFIRMED_AT,1,10) between '2022-08-01' and '2022-08-31'
)

select
    REGIST_DATE
    ,WK
    ,coalesce(TOTAL,0)
from
    base_date2 t1
left join data t2 on t1.REGIST_DATE = t2.d
ORDER BY REGIST_DATE