WITH RECURSIVE dates(date) AS (
  VALUES('2022-08-01')
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < '2022-08-31'
), week AS (
    SELECT
        date,
        substr('日月火水木金土', strftime("%w", date)*1 +1, 1) AS WK
    FROM dates
),
confirm AS (
    SELECT
        DATE(CONFIRMED_AT) AS REGIST_DATE,
        COUNT(DISTINCT USER_CODE) AS TOTAL
    FROM USERS
    WHERE DATE(CONFIRMED_AT)>= "2022-08-01" AND DATE(CONFIRMED_AT)<= "2022-08-31" AND VALID_FLG='1'
    GROUP BY DATE(CONFIRMED_AT), substr('日月火水木金土', strftime("%w", DATE(CONFIRMED_AT))*1 +1, 1)
    ORDER BY REGIST_DATE
)

SELECT d.date AS REGIST_DATE, w.WK, coalesce(TOTAL, 0) AS TOTAL
FROM dates d
JOIN week w ON d.date=w.date
LEFT JOIN confirm c ON d.date=c.REGIST_DATE