with mst_digit as (
    select 0 as digit
    union
    select 1 as digit
    union 
    select 2 as digit
    union 
    select 3 as digit
    union 
    select 4 as digit
    union 
    select 5 as digit
    union 
    select 6 as digit
    union 
    select 7 as digit
    union 
    select 8 as digit
    union 
    select 9 as digit   
)
, target as (
    SELECT
        (d1.digit + (d2.digit * 10)) AS Number
       -- ,'2022-08-' || SUBSTR('00' || cast((d1.digit + (d2.digit * 10)) as text),-2,2)
        ,date('2022-08-' || SUBSTR('00' || cast((d1.digit + (d2.digit * 10)) as text),-2,2)) as target_date
    FROM (mst_digit d1 join mst_digit d2)
    where Number>=1 and Number<=31
    order by Number
)
select
    target_date as REGIST_DATE
    ,substr('日月火水木金土', strftime("%w", target_date, 'localtime')+1, 1) as WK
    ,ifnull(CNT,0) as TOTAL
from  
    target as t1
left join(
    select
        strftime('%Y-%m-%d', CONFIRMED_AT) as REGIST_DATE
        ,count(*) as CNT
    from
        USERS
    where 
        VALID_FLG='1'
    and
        strftime('%Y-%m', CONFIRMED_AT) = '2022-08'
    group by 
        strftime('%Y-%m-%d', CONFIRMED_AT)
) as t2
    on t2.REGIST_DATE = t1.target_date
    group by 
        target_date
    order by
       target_date