WITH RECURSIVE calendar(calendar_date) AS (
    SELECT
        -- ここが始まりの日付
        DATE('2022-08-01') AS calendar_date
    UNION ALL
    -- UNION ALL 以下が再帰処理部分
    SELECT
        DATE(calendar_date, '+1 day') AS calendar_date
    FROM calendar
    WHERE
        -- ここが終わりの日付
        calendar_date < DATE('2022-08-31')
) 


select calendar.calendar_date REGIST_DATE,substr('日月火水木金土', strftime("%w", calendar.calendar_date, 'localtime')+1, 1) as WK, sum(
case when valid_flg = '1' then 1 else 0 end
) TOTAL  from calendar
left join (select * from users where valid_flg='1') users
on calendar.calendar_date = substr(users.confirmed_at,1,10) 

group by 1
order by 1
;