WITH RECURSIVE
  cnt(x) AS (VALUES(julianday(replace('2022/08/01', '/', '-') ))
  UNION ALL
   SELECT x+1 FROM cnt WHERE x < julianday(replace('2022/08/31', '/', '-') )),
TOTAL AS (
  SELECT
    COUNT(CONFIRMED_AT) AS TOTAL,
    DATE(datetime(CONFIRMED_AT)) AS REGIST_DATE
  FROM USERS
  WHERE
    VALID_FLG='1'
  GROUP BY
    REGIST_DATE
)
SELECT
  strftime('%Y-%m-%d', x) AS REGIST_DATE,
  substr('日月火水木金土', strftime("%w", x, 'localtime')+1, 1) AS WK,
  COALESCE(TOTAL.TOTAL, 0) AS TOTAL
FROM cnt
LEFT JOIN TOTAL
ON
  TOTAL.REGIST_DATE = strftime('%Y-%m-%d', cnt.x)
ORDER BY
  strftime('%Y-%m-%d', x) ASC;
;