-- ステップ1: 8月の日付リストを作成する
WITH RECURSIVE date_list AS (
    SELECT DATE('2022-08-01') AS regist_date
    UNION ALL
    SELECT DATE(regist_date, '+1 day')
    FROM date_list
    WHERE regist_date < DATE('2022-08-31')
)
SELECT 
    d.REGIST_DATE
    ,CASE strftime('%w', d.regist_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(t.total, 0) AS TOTAL
FROM 
    date_list d
LEFT JOIN (
    SELECT 
        DATE(CONFIRMED_AT) AS regist_date,
        COUNT(*) AS total
    FROM 
        USERS
    WHERE 
        CONFIRMED_AT LIKE '2022-08-%'
        and VALID_FLG = '1'
    GROUP BY 
        DATE(CONFIRMED_AT)
) t
ON d.regist_date = t.regist_date
ORDER BY 
    d.regist_date;