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
	,(CASE
			WHEN strftime('%w', c.calendar_date)='0' THEN '日'
			WHEN strftime('%w', c.calendar_date)='1' THEN '月'
			WHEN strftime('%w', c.calendar_date)='2' THEN '火'
			WHEN strftime('%w', c.calendar_date)='3' THEN '水'
			WHEN strftime('%w', c.calendar_date)='4' THEN '木'
			WHEN strftime('%w', c.calendar_date)='5' THEN '金'
			WHEN strftime('%w', c.calendar_date)='6' THEN '土'
	END) as WK
	, ifnull (CNT,0) as TOTAL
from
	calendar c
	left outer join
	wk_list w
	on
		c.calendar_date = w.ymd