WITH RECURSIVE date_list (date, WK) AS
(
    SELECT '2022-08-01',
    CASE STRFTIME('%w', DATE('2022-08-01'))
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土' END
  UNION ALL
    SELECT date(date, '+1 days'),
    CASE STRFTIME('%w', DATE(date(date, '+1 days')))
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土' END
    FROM date_list
    WHERE date <= '2022-08-30'
)

, D AS
( SELECT
  DATE(CONFIRMED_AT) AS REGIST_DATE,
  MAX(CASE STRFTIME('%w', DATE(CONFIRMED_AT))
    WHEN '0' THEN '日'
    WHEN '1' THEN '月'
    WHEN '2' THEN '火'
    WHEN '3' THEN '水'
    WHEN '4' THEN '木'
    WHEN '5' THEN '金'
    WHEN '6' THEN '土' END) AS WK,
  COUNT(DATE(CONFIRMED_AT)) AS TOTAL
FROM
  USERS
WHERE
  VALID_FLG = '1'
GROUP BY
  DATE(CONFIRMED_AT)
HAVING
  DATE(CONFIRMED_AT) BETWEEN '2022-08-01' AND '2022-08-31'
ORDER BY
  REGIST_DATE
)

SELECT
  date_list.DATE AS REGIST_DATE,
  date_list.WK AS WK,
  COALESCE(D.TOTAL,0) AS TOTAL
FROM
  date_list
FULL OUTER JOIN
  D
ON
  date_list.date = D.REGIST_DATE;