WITH DATES as (
    select 
        date( (select min(date(CONFIRMED_AT)) from users), '+' || (rownum - 1) || ' days') as CALDATE
    from (
        select row_number() over win as rownum
        from users
        window win as (order by user_code)
    )
)
, USR as (
select 
*
from
users
where
valid_flg = '1'
AND CONFIRMED_AT IS NOT NULL
)
select 
    CALDATE as REGIST_DATE
    , case strftime('%w', CALDATE)
            when '0' then '日'
            when '1' then '月'
            when '2' then '火'
            when '3' then '水'
            when '4' then '木'
            when '5' then '金'
            when '6' then '土'
            else 'undefined'
        end as WK
    , IFNULL( TOTAL, 0 ) as TOTAL
From
DATES LEFT JOIN  
(
    select
        date(CONFIRMED_AT) CONFIRMED_AT, COUNT(*) as TOTAL
    from
        USR
    GROUP BY
        date(CONFIRMED_AT)
) TOTAL ON CALDATE = CONFIRMED_AT
WHERE
    CALDATE BETWEEN (select min(date(CONFIRMED_AT)) from USR)
            AND (select max(date(CONFIRMED_AT)) from USR)