WITH RECURSIVE CALENDAR(CALENDAR_DATE) AS (
    SELECT
        DATE('2022-08-01') AS CALENDAR_DATE
    UNION ALL
    SELECT
        DATE(CALENDAR_DATE, '+1 day') AS CALENDAR_DATE
    FROM CALENDAR
    WHERE
        CALENDAR_DATE < DATE('2022-08-31')
)
,Q AS (
  SELECT DATE(CONFIRMED_AT) REGIST_DATE
        ,COUNT(*) CNT
    FROM USERS
   WHERE VALID_FLG = '1'
   GROUP BY DATE(CONFIRMED_AT)
)
SELECT C.CALENDAR_DATE AS REGIST_DATE
      ,SUBSTR('日月火水木金土', STRFTIME("%w", C.calendar_date)+1, 1) AS WK
      ,IFNULL(Q.CNT, 0) AS TOTAL
FROM CALENDAR C
LEFT OUTER JOIN Q
  ON C.CALENDAR_DATE = Q.REGIST_DATE
ORDER BY REGIST_DATE