select 
	v.dt as REGIST_DATE,
	case
		when STRFTIME('%w' , dt) = '0' then '日'
		when STRFTIME('%w' , dt) = '1' then '月'
		when STRFTIME('%w' , dt) = '2' then '火'
		when STRFTIME('%w' , dt) = '3' then '水'
		when STRFTIME('%w' , dt) = '4' then '木'
		when STRFTIME('%w' , dt) = '5' then '金'
		when STRFTIME('%w' , dt) = '6' then '土'
	end as WK,
	sum(V.cnt) as TOTAL
from
(
	select 
		DATE(SUBSTR(USERS.CONFIRMED_AT, 0, 11)) as dt, 
		COUNT(USERS.VALID_FLG) as cnt
	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 
		dt
	union select DATE('2022-08-01') as dt, 0 as cnt
	union select DATE('2022-08-02') as dt, 0 as cnt
	union select DATE('2022-08-03') as dt, 0 as cnt
	union select DATE('2022-08-04') as dt, 0 as cnt
	union select DATE('2022-08-05') as dt, 0 as cnt
	union select DATE('2022-08-06') as dt, 0 as cnt
	union select DATE('2022-08-07') as dt, 0 as cnt
	union select DATE('2022-08-08') as dt, 0 as cnt
	union select DATE('2022-08-09') as dt, 0 as cnt
	union select DATE('2022-08-10') as dt, 0 as cnt
	union select DATE('2022-08-11') as dt, 0 as cnt
	union select DATE('2022-08-12') as dt, 0 as cnt
	union select DATE('2022-08-13') as dt, 0 as cnt
	union select DATE('2022-08-14') as dt, 0 as cnt
	union select DATE('2022-08-15') as dt, 0 as cnt
	union select DATE('2022-08-16') as dt, 0 as cnt
	union select DATE('2022-08-17') as dt, 0 as cnt
	union select DATE('2022-08-18') as dt, 0 as cnt
	union select DATE('2022-08-19') as dt, 0 as cnt
	union select DATE('2022-08-20') as dt, 0 as cnt
	union select DATE('2022-08-21') as dt, 0 as cnt
	union select DATE('2022-08-22') as dt, 0 as cnt
	union select DATE('2022-08-23') as dt, 0 as cnt
	union select DATE('2022-08-24') as dt, 0 as cnt
	union select DATE('2022-08-25') as dt, 0 as cnt
	union select DATE('2022-08-26') as dt, 0 as cnt
	union select DATE('2022-08-27') as dt, 0 as cnt
	union select DATE('2022-08-28') as dt, 0 as cnt
	union select DATE('2022-08-29') as dt, 0 as cnt
	union select DATE('2022-08-30') as dt, 0 as cnt
	union select DATE('2022-08-31') as dt, 0 as cnt
) V 
group by 
	REGIST_DATE, WK
order by 
	REGIST_DATE