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 AS(
    SELECT DATE("2022-07-31", "+" ||ROWID||" day") as REGIST_DATE
    FROM USERS 
    ORDER BY REGIST_DATE
    LIMIT 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