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
        base.calendar_date AS REGIST_DATE,
        (
            CASE
                strftime('%w', base.calendar_date)
                WHEN '0' THEN '日'
                WHEN '1' THEN '月'
                WHEN '2' THEN '火'
                WHEN '3' THEN '水'
                WHEN '4' THEN '木'
                WHEN '5' THEN '金'
                WHEN '6' THEN '土'
                ELSE ''
            END
        ) AS WEEK,
        (
            CASE
                VALID_FLG
                WHEN '1' THEN COUNT(USER_CODE)
                WHEN '0' THEN 0
                ELSE 0
            END
        ) AS TOTAL
    FROM
        calendar AS base
        LEFT JOIN (
            SELECT
                CONFIRMED_AT,
                DATE(CONFIRMED_AT) AS t_date,
                VALID_FLG,
                USER_CODE
            FROM
                USERS
            WHERE
                VALID_FLG = '1'
                AND CONFIRMED_AT > "2022-07-31 23:59:59"
                AND CONFIRMED_AT < "2022-09-01 00:00:00"
        ) AS condition ON condition.t_date = base.calendar_date
    GROUP BY
        base.calendar_date
    ORDER BY
        REGIST_DATE ASC;