SELECT DAYS.REGIST_DATE, DAYS.WK, COALESCE(CONF.CNT, 0) AS TOTAL
FROM (
  WITH RECURSIVE
    cnt(d) AS (VALUES(julianday('2022-08-01'))
    UNION ALL
    SELECT d+1 FROM cnt WHERE d < julianday('2022-08-31'))
  
  SELECT strftime('%Y-%m-%d', d) AS REGIST_DATE,
  CASE strftime('%w', d)
    WHEN '0' then '日'
    WHEN '1' then '月'
    WHEN '2' then '火'
    WHEN '3' then '水'
    WHEN '4' then '木'
    WHEN '5' then '金'
    WHEN '6' then '土'
    ELSE '不明'
    END AS WK
  FROM cnt
) DAYS
LEFT OUTER JOIN (
  SELECT strftime('%Y-%m-%d', CONFIRMED_AT) AS CONFIRMED_DATE,
  COUNT(1) AS CNT
  FROM USERS
  WHERE VALID_FLG = '1'
  GROUP BY CONFIRMED_DATE
) AS CONF
ON DAYS.REGIST_DATE = CONF.CONFIRMED_DATE
;