WITH date_range AS(
    SELECT
        date('2022-08-01') AS date
    UNION ALL
    SELECT
        date(date, '+1 day')
    FROM
        date_range
    WHERE
        date < '2022-08-31'
),
date_range_w AS(
    select
        date_range.date as REGIST_DATE,
        case strftime('%w', date_range.date)
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            when '6' then '土'
        end as WK
    from
        date_range
),
USERS_CNT AS(
    select
        strftime('%Y-%m-%d',CONFIRMED_AT) as REGIST_DATE,
        count(*) as TOTAL
    from
        USERS
    where
        VALID_FLG = '1'
    and REGIST_DATE between '2022-08-01' and '2022-08-31'
    group by
        REGIST_DATE
)
select date_range_w.REGIST_DATE
      ,date_range_w.WK
      ,ifnull(USERS_CNT.TOTAL,0)
from date_range_w
left join USERS_CNT
on date_range_w.REGIST_DATE = USERS_CNT.REGIST_DATE
order by date_range_w.REGIST_DATE