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,
    count(u.USER_CODE) as TOTAL
from
    calendar c
    left join
        USERS u
        on c.REGIST_DATE = date(u.CONFIRMED_AT)
            and
            VALID_FLG = '1'
group by
    c.REGIST_DATE
order by
    c.REGIST_DATE;