with tmp(REGIST_DATE) as(
values('2022-08-01'),
      ('2022-08-02'),
      ('2022-08-03'),
      ('2022-08-04'),
      ('2022-08-05'),
      ('2022-08-06'),
      ('2022-08-07'),
      ('2022-08-08'),
      ('2022-08-09'),
      ('2022-08-10'),
      ('2022-08-11'),
      ('2022-08-12'),
      ('2022-08-13'),
      ('2022-08-14'),
      ('2022-08-15'),
      ('2022-08-16'),
      ('2022-08-17'),
      ('2022-08-18'),
      ('2022-08-19'),
      ('2022-08-20'),
      ('2022-08-21'),
      ('2022-08-22'),
      ('2022-08-23'),
      ('2022-08-24'),
      ('2022-08-25'),
      ('2022-08-26'),
      ('2022-08-27'),
      ('2022-08-28'),
      ('2022-08-29'),
      ('2022-08-30'),
      ('2022-08-31'))
SELECT tmp.REGIST_DATE
,substr('日月火水木金土', strftime("%w",tmp.REGIST_DATE)+1, 1) AS WK
,IfNULL(cnt.TOTAL,0)
FROM tmp LEFT JOIN
(
	SELECT strftime('%Y-%m-%d',CONFIRMED_AT) AS CONFIRMED_AT 
	,COUNT(*) AS TOTAL
	FROM USERS
	WHERE CONFIRMED_AT BETWEEN '2022-08-01 00:00:00' AND '2022-08-31 23:59:59'
	AND VALID_FLG = '1'
	GROUP BY strftime('%Y-%m-%d',CONFIRMED_AT)
) cnt

ON cnt.CONFIRMED_AT = tmp.REGIST_DATE
ORDER by 1