WITH RECURSIVE DATE_TABLE(DATE_VALUE) AS ( 
    SELECT
       '2022-08-01'
    UNION ALL 
    SELECT
        date(DATE_VALUE, '+1 days')
    FROM
        DATE_TABLE 
    WHERE
        DATE_VALUE < '2022-08-31'
)

, grouping AS (
    select 
        date(users.CONFIRMED_AT) as CONFIRMED_AT
        ,count (*) as count
    from
        users
    where VALID_FLG = '1'
    group by date(users.CONFIRMED_AT)
) 

select DATE_TABLE.DATE_VALUE as REGIST_DATE
    , substr('日月火水木金土', strftime("%w",DATE_TABLE.DATE_VALUE)+1, 1)  as WK
    , coalesce(count,0) as total

from DATE_TABLE
left join grouping on DATE_TABLE.DATE_VALUE = date(grouping.CONFIRMED_AT)