with recursive days(value) as (
select 0
union all
select value + 1 from days
limit 31
)
, calendar(dt) as (
select date('2022-08-01', '+' || value || ' days')
from days
)
, weekday(value, name) as (
select 0, '日'
union all select 1, '月'
union all select 2, '火'
union all select 3, '水'
union all select 4, '木'
union all select 5, '金'
union all select 6, '土'
)
, calc as (
select date(confirmed_at) as dt, count(*) as cnt
from users
where valid_flg = 1
group by date(confirmed_at)
)
select dt as REGIST_DATE, weekday.name as WK, coalesce(cnt, 0) as TOTAL
from calendar
left join calc
using(dt)
left join weekday
on cast(strftime('%w', dt) as integer) = weekday.value