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-08-01"
            AND CONFIRMED_AT < "2022-09-01"
    ) AS condition ON condition.t_date = base.calendar_date
GROUP BY
    base.calendar_date
ORDER BY
    REGIST_DATE ASC;