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
    , 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)