WITH RECURSIVE
	calendar(calendar_date)
AS
	(
		select
		DATE('2022-08-01') AS calendar_date
		union all
		select
			DATE(calendar_date, '+1 day') AS calendar_date
		from calendar
		where
			calendar_date < DATE('2022-08-31')
	)
,
wk_list (ymd, CNT)
AS
	(
	select
		DATE(CONFIRMED_AT) as ymd
		, count(USER_CODE) as CNT
	from
		USERS
	where
		VALID_FLG='1'
		and CONFIRMED_AT >= '2022-08-01' and CONFIRMED_AT < '2022-09-01'
	group by
		DATE(CONFIRMED_AT)
	order by
		DATE(CONFIRMED_AT)
	)

select
	c.calendar_date as REGIST_DATE
	,strftime('%w', c.calendar_date) as WK
	, ifnull (CNT,0) as TOTAL
from
	calendar c
	left outer join
	wk_list w
	on
		c.calendar_date = w.ymd