WITH RECURSIVE calendar AS (
    SELECT
        DATE('2022-08-01') AS REGIST_DATE
    UNION ALL
    SELECT
        DATE(REGIST_DATE, '+1 day') AS REGIST_DATE
    FROM calendar
    WHERE
        REGIST_DATE < DATE('2022-08-31')
)

select
    c.REGIST_DATE,
    case strftime('%w', c.REGIST_DATE)
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            when '6' then '土'
            else 'undefined'
        end as WK,
    ifnull(u.TOTAL, 0) as TOTAL
from
    calendar c
    left join (
        select
            date(CONFIRMED_AT) as REGIST_DATE,
            count(USER_CODE) as TOTAL
            
        from
            USERS
        where
            date(CONFIRMED_AT) like '2022-08%'
            and
            VALID_FLG = '1'
        group by
            date(CONFIRMED_AT)
    ) u
    on c.REGIST_DATE = u.REGIST_DATE
order by
    c.REGIST_DATE;