WITH RECURSIVE DATES(x) AS (
  VALUES('2022-08-01')
  UNION ALL
  SELECT date(x, '+1 day')
  FROM dates
  WHERE x < '2022-08-31'
)
SELECT 
    strftime('%Y-%m-%d', x) AS "REGIST_DATE" ,
    (CASE 
        WHEN strftime('%w', x) == "0" THEN "日"
        WHEN strftime('%w', x) == "1" THEN "月"
        WHEN strftime('%w', x) == "2" THEN "火"
        WHEN strftime('%w', x) == "3" THEN "水"
        WHEN strftime('%w', x) == "4" THEN "木"
        WHEN strftime('%w', x) == "5" THEN "金"
        WHEN strftime('%w', x) == "6" THEN "土"
        ELSE strftime('%w', x)
    END) AS "WK",
    CASE WHEN temp_users.count IS NULL THEN 0 ELSE temp_users.count END AS "TOTAL"
FROM DATES
LEFT OUTER JOIN (
    SELECT 
        strftime('%Y-%m-%d', CONFIRMED_AT) AS "CONFIRMED_AT",
        count(*) AS "count"
    FROM USERS
    WHERE VALID_FLG = 1
    AND strftime('%Y-%m-%d', CONFIRMED_AT) >= "2022-08-01" AND strftime('%Y-%m-%d', CONFIRMED_AT) <= "2022-08-31"
    GROUP BY strftime('%Y-%m-%d', CONFIRMED_AT)
) AS temp_users ON temp_users.CONFIRMED_AT = strftime('%Y-%m-%d', x)