with cleaned_users as (
select
*
from users
where CONFIRMED_AT IS NOT NULL
AND VALID_FLG='1'
AND strftime('%Y-%m-%d', CREATED_AT) >= '2022-08-01'
AND strftime('%Y-%m-%d', CREATED_AT) < '2022-09-01'
)
select strftime('%Y-%m-%d', CONFIRMED_AT) as REGIST_DATE
, case when strftime('%w',CONFIRMED_AT)='0' then '日'
when strftime('%w',CONFIRMED_AT)='1' then '月'
when strftime('%w',CONFIRMED_AT)='2' then '火'
when strftime('%w',CONFIRMED_AT)='3' then '水'
when strftime('%w',CONFIRMED_AT)='4' then '木'
when strftime('%w',CONFIRMED_AT)='5' then '金'
else '土' end
as WK
, count(0) as TOTAL
from cleaned_users
group by 1,2