with recursive generate_series(x) as (
    select
        1
    union
    all
    select
        x + 1
    from
        generate_series
    limit
        31
), t1 as (
    select
        USER_CODE,
        date(CONFIRMED_AT) as REGIST_DATE
    from
        USERS
    where
        VALID_FLG == "1"
        and "2022-08-01" <= CONFIRMED_AT
        and CONFIRMED_AT <= "2022-08-31"
),
t2 as (
    select
        REGIST_DATE,
        count(*) as TOTAL
    from
        t1
    group by
        REGIST_DATE
),
t3 as(
    select
        date(
            unixepoch("2022-07-31") + 1 + 86400 * x,
            "unixepoch"
        ) as REGIST_DATE
    from
        generate_series
)
select
    t3.REGIST_DATE as REGIST_DATE,
    substr("日月火水木金土", strftime("%w", t3.REGIST_DATE) + 1, 1) as WK,
    ifnull(t2.TOTAL, 0) as TOTAL
from
    t2
    right outer join t3 on t2.REGIST_DATE == t3.REGIST_DATE
order by
    t3.REGIST_DATE