题解 | 牛客每个人最近的登录日期(四)
牛客每个人最近的登录日期(四)
https://www.nowcoder.com/practice/e524dc7450234395aa21c75303a42b0a
with tt as (
select user_id, date, min(date) over (partition by user_id) as first_day
from login
),
tb as (
select
user_id,
if(date = first_day, 1, 0) as is_first,
date,
first_day
from tt
)
select date,
ifnull(count(distinct case when is_first = 1 then user_id else null end),0) as new
from tb
group by date