题解 | 牛客每个人最近的登录日期(五)

with
t1 as(
    select
        user_id,
        date,
        min(date)over(partition by user_id order by date) as first_date
    from
        login
),
t2 as(
    select
        user_id,
        date,
        first_date,
        (
            case
                when date=first_date then 1
                else 0
            end
        ) as is_first_date,
        (
            case
                when timestampdiff(day,first_date,date)=1 then 1
                else 0
            end
        ) as is_second_date
    from
        t1
),
t3 as(
    select
        date,
        sum(is_first_date) as first_come,
        sum(is_second_date) as second_come
    from
        t2
    group by
        date
),
t4 as(
    select
        date,
        first_come,
        lead(second_come,1)over(order by date) as second_come
    from
        t3
),
t5 as(
    select
        date,
        (
            case
                when round(second_come/first_come,3) is not null then round(second_come/first_come,3)
                else 0.000
            end
        ) as second_come
    from
        t4
    order by
        date
)
select * from t5

可能看起来有点混乱，但是顺下来还是比较自然的。增加了辅助列