WITH DATES_AUGUST AS (
    SELECT
        '2022-08-01' AS _date
    UNION ALL
    SELECT
        DATE(_date, '+1 days')
    FROM
        DATES_AUGUST
    WHERE
        _date < '2022-08-31'
)
, USERS_VALID AS (
    SELECT
        USER_CODE
        , CONFIRMED_AT
        , strftime('%Y-%m-%d', CONFIRMED_AT) AS CONFIRMED_DATE
    FROM
        USERS
    WHERE
        VALID_FLG = '1' -- 有効
    ORDER BY
        CONFIRMED_AT ASC    -- 昇順で確認
)
SELECT
    _date AS REGIST_DATE
    , CASE
        WHEN strftime('%w', _date) = '0' THEN '日'  -- 曜日番号から曜日を判定
        WHEN strftime('%w', _date) = '1' THEN '月'
        WHEN strftime('%w', _date) = '2' THEN '火'
        WHEN strftime('%w', _date) = '3' THEN '水'
        WHEN strftime('%w', _date) = '4' THEN '木'
        WHEN strftime('%w', _date) = '5' THEN '金'
        WHEN strftime('%w', _date) = '6' THEN '土'
        END 
        AS WK
    , SUM(CASE WHEN USER_CODE IS NOT NULL THEN 1 ELSE 0 END) AS TOTAL
FROM
    DATES_AUGUST
LEFT JOIN
    USERS_VALID ON USERS_VALID.CONFIRMED_DATE = DATES_AUGUST._date
GROUP BY
    _date
;