WITH RECURSIVE calendar(calendar_date) AS (
    SELECT
        DATE('2022-08-01') AS calendar_date
    UNION
    ALL
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM
        calendar
    WHERE
        calendar_date < DATE('2022-08-31')
) -- ここが実際のSELECT文
SELECT
    strftime('%Y-%m-%d', calendar.calendar_date) as REGIST_DATE,
    case
        strftime('%w', calendar.calendar_date)
        when '0' then '日'
        when '1' then '月'
        when '2' then '火'
        when '3' then '水'
        when '4' then '木'
        when '5' then '金'
        when '6' then '土'
    end as WK,
    COALESCE(total.TOTAL, 0) as TOTAL
FROM
    calendar
    left join(
        select
            strftime('%Y-%m-%d', CONFIRMED_AT) as calendar_date,
            COUNT(USER_CODE) as TOTAL
        from
            USERS
        where
            CONFIRMED_AT between '2022-08-01 00:00:00'
            and '2022-08-31 23:59:59'
            and VALID_FLG = '1'
        group by
            strftime('%Y-%m-%d', CONFIRMED_AT)
    ) total on strftime('%Y-%m-%d', calendar.calendar_date) = total.calendar_date
order by
    REGIST_DATE