题解 | #计算每天的新用户占比#

select
    tmp.login_date as dt,
    count(distinct tmp.uid) as total_user_num,
    concat(format(round(sum(case when user_type = 'new' then 1 else 0 end)/count(distinct tmp.uid),3)*100,1),"%") as new_user_rate
from
    (select
        a.uid,
        a.login_date,
        case when a.login_date = first_login_date then 'new' else 'old' end as user_type
    from
        user_login_tb a
    left join
        (select
            uid,
            min(login_date) as first_login_date
        from
            user_login_tb
        group by 
            1) b on a.uid = b.uid) tmp
group by
    1
order by 
    1