WITH AGG AS(
    SELECT substr(CONFIRMED_AT, 0,11) as REGIST_DATE_tmp,
    count(user_code) as TOTAL
    FROM USERS
    WHERE VALID_FLG = "1"
    AND REGIST_DATE_tmp LIKE "2022-08%"
    GROUP BY REGIST_DATE_tmp
    
),
AUG(REGIST_DATE) AS(
    SELECT DATE('2022-08-01') AS REGIST_DATE
    UNION ALL
    SELECT DATE(REGIST_DATE, '+1 day') AS REGIST_DATE
    FROM AUG
    WHERE REGIST_DATE < DATE('2022-08-31')
)
SELECT
AUG.REGIST_DATE,
substr('日月火水木金土', strftime("%w", AUG.REGIST_DATE) +1, 1) AS WK,
IFNULL(TOTAL, 0) AS TOTAL
FROM AUG
LEFT OUTER JOIN AGG
ON REGIST_DATE = REGIST_DATE_tmp
ORDER BY REGIST_DATE ASC