WITH target_user_confirm_date AS (
    SELECT
        date(CONFIRMED_AT) AS CONFIRMED_DATE
    FROM
        USERS
    WHERE
        VALID_FLG = '1'
),
seq_table AS (
    SELECT 0 AS seq
    UNION ALL
    SELECT seq + 1 FROM seq_table WHERE seq < 100 -- 適当な連続した数値データを生成
),
august AS (
    SELECT
        date('2022-08-01', '+' || seq || ' day' ) as august_date,
        substr('日月火水木金土',
                strftime("%w", date(date('2022-08-01', '+' || seq || ' day' )), 'localtime') +1, 1) as weekname
    FROM
        seq_table  
    WHERE
        seq < 31 -- 0〜30まで
)
SELECT
    august.august_date AS REGIST_DATE,
    august.weekname AS WK,
    count(target_user_confirm_date.CONFIRMED_DATE) AS TOTAL
FROM
    august
    LEFT JOIN target_user_confirm_date
        ON august.august_date = target_user_confirm_date.CONFIRMED_DATE
GROUP BY
    august.august_date,
    august.weekname
ORDER BY
    REGIST_DATE
;