with date_table as(
    
    select
        '2022-08-01' as cldate, 
        strftime('%w', '2022-08-01') as wkno, 
        substr('日月火水木金土', strftime('%w', '2022-08-01') + 1, 1) as week
    
    union all
    
    select 
        date (cldate, '+1 days'),
        strftime('%w', date(cldate, '+1 days')),
        substr(
            '日月火水木金土', 
            strftime('%w', date(cldate, '+1 days')) + 1,
            1
            )
    
    from date_table
    
    where cldate < '2022-08-31'
)

select 
    cldate as REGIST_DATE,
    week as WK,
    count(user_code) as TOTAL
    
from date_table
left outer join users
on date(users.confirmed_at) = date_table.cldate
and valid_flg = '1'

group by cldate

order by cldate asc

;