with tmp(REGIST_DATE,WK) 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
,tmp.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 VALID_FLG = '1'
	GROUP BY strftime('%Y-%m-%d',CONFIRMED_AT)
) cnt
ON cnt.CONFIRMED_AT = tmp.REGIST_DATE
ORDER by tmp.REGIST_DATE