with all_days as (
    select
        date(CONFIRMED_AT) as REGIST_DATE
    from
        USERS
    where
        substr(CONFIRMED_AT, 1,7) = '2022-08'
),
log as (
    select
        date(CONFIRMED_AT) as REGIST_DATE,
        USER_CODE
    from
        USERS
    where
        VALID_FLG = 1
        and substr(CONFIRMED_AT, 1,7) = '2022-08'
)
select
    all_days.REGIST_DATE,
    substr('日月火水木金土', strftime("%w", all_days.REGIST_DATE, 'localtime')+1, 1) AS WK,
    COUNT(DISTINCT USER_CODE) as TOTAL
from
    all_days
left join
    log
on
    all_days.REGIST_DATE = log.REGIST_DATE
where
    all_days.REGIST_DATE is not null
group by
    log.REGIST_DATE
order by
    all_days.REGIST_DATE asc