WITH RECURSIVE
    cnt(x) AS (VALUES(julianday('2022-08-01'))
    UNION ALL
    SELECT x+1 FROM cnt WHERE x < julianday('2022-08-31')
)
, TMP as (
    SELECT 
    strftime('%Y-%m-%d', x) AS d
    , (CASE cast( strftime('%w', x) as integer)
    WHEN 0  THEN '日'
    WHEN 1 THEN '月'
    WHEN 2 THEN '火'
    WHEN 3 THEN '水'
    WHEN 4 THEN '木'
    WHEN 5 THEN '金'
    ELSE '土' END) AS WK
FROM cnt)
, u AS (
    SELECT 
    DATE(confirmed_at) as r
    , COUNT(user_code) as total
    FROM USERS u
    WHERE u.confirmed_at IS NOT NULL 
    AND DATE(u.confirmed_at) BETWEEN DATE('2022-08-01') AND DATE('2022-08-31')
    AND VALID_FLG='1'
    GROUP BY r
)
, src AS (
    SELECT tmp.d AS REGIST_DATE
    , tmp.wk AS WK
    , u.total AS TOTAL
FROM tmp
LEFT OUTER JOIN u
ON u.r = tmp.d
)
SELECT REGIST_DATE, WK, IFNULL(TOTAL, 0) AS TOTAL FROM SRC