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', '/', '-') ))

SELECT D.DAY AS REGIST_DATE,
(CASE WHEN STRFTIME("%w", CONFIRMED_AT) = "0" THEN "日"
WHEN STRFTIME("%w", CONFIRMED_AT) = "1" THEN "月"
WHEN STRFTIME("%w", CONFIRMED_AT) = "2" THEN "火"
WHEN STRFTIME("%w", CONFIRMED_AT) = "3" THEN "水"
WHEN STRFTIME("%w", CONFIRMED_AT) = "4" THEN "木"
WHEN STRFTIME("%w", CONFIRMED_AT) = "5" THEN "金"
ELSE "土" END) AS WK,
SUM(CASE WHEN VALID_FLG = "1" THEN 1 ELSE 0 END) AS TOTAL
FROM (SELECT strftime('%Y-%m-%d', x) AS DAY FROM cnt) AS D LEFT JOIN USERS ON D.DAY = DATE(USERS.CONFIRMED_AT)
GROUP BY D.DAY
ORDER BY D.DAY;