WITH RECURSIVE cal(cdate) AS (
    SELECT
        DATE('2022-08-01') AS cdate
    UNION ALL
    SELECT
        DATE(cdate, '+1 day') AS cdate
    FROM cal
    WHERE
        cdate < DATE('2022-08-31')
)
select strftime('%Y-%m-%d',c.cdate) REGIST_DATE
, case strftime('%w', c.cdate)
    when '0' then '日'
    when '1' then '月'
    when '2' then '火'
    when '3' then '水'
    when '4' then '木'
    when '5' then '金'
    when '6' then '土'
    else ''
end as WK
, count(u.USER_CODE) TOTAL
from cal c
     left join USERS u
       on strftime('%Y-%m-%d',u.CONFIRMED_AT) = strftime('%Y-%m-%d',c.cdate ) 
       and u.VALID_FLG = '1'
group by
strftime('%Y-%m-%d',c.cdate)
, case strftime('%w', c.cdate)
    when '0' then '日'
    when '1' then '月'
    when '2' then '火'
    when '3' then '水'
    when '4' then '木'
    when '5' then '金'
    when '6' then '土'
    else ''
end
order by REGIST_DATE
;