WITH SEQ AS (SELECT 0 AS no UNION ALL SELECT no + 1 AS no FROM SEQ WHERE no + 1 < 31)
   , t1 AS (SELECT no, DATE('2022-08-01', '+' || no || ' day') AS dt FROM seq)
SELECT dt AS REGIST_DATE
     , substr('月火水木金土日', (no % 7)+1,1) AS WK
     , IFNULL(COUNT(us.USER_CODE), 0) AS TOTAL
FROM t1
     LEFT JOIN
     USERS AS us ON DATE(us.CONFIRMED_AT) == t1.dt
         AND us.VALID_FLG = '1'
group by dt,no;
;