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

做是做出来了，写到最后我自己都乱了，无语，先记录一下。

这道题可以类比之前做过一道 #每月及截止当月的答题情况# 的这个。

难点在于：

判断哪些是新增用户。

这个代码应该可以精简，之后会修改。

select distinct
    login_date as dt,
    count(distinct uid) as total_user_num,
    concat (
        round(
            count(new_first_active_day) / count(distinct uid) * 100,
            1
        ),
        "%"
    ) as new_user_rate
from
    (
        select
            uid,
            login_date,
            (
                case
                    when login_date = first_active_day then first_active_day
                    else null
                end
            ) as new_first_active_day
        from
            (
                select
                    uid,
                    login_date,
                    min(login_date) over (
                        partition by
                            uid
                    ) as first_active_day
                from
                    user_login_tb
            ) as tb
    ) as tc
group by
    login_date
order by
    login_date