题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
- 查询出有新用户登录的日期以及登录人数
select date,(select count(*)
from (select min(user_id),date
from login
group by user_id) as t
where t.date = l.date
) as new_counts
from login l
group by date
order by date) as table1
| date | count |
|---|---|
| 2020-10-12 | 3 |
| 2020-10-13 | 0 |
| 2020-10-14 | 1 |
| 2020-10-15 | 0 |
- 查询出每个日期新用户次日继续登录的人数
select count(*) as new
from (select min(user_id),date
from login
group by user_id) as t
where t.date = DATE_ADD(l.date,INTERVAL 1 DAY)
| 名称 | 缩写 |
|---|---|
| 2020-10-12 | 2 |
| 2020-10-13 | 0 |
| 2020-10-14 | 1 |
| 2020-10-15 | 0 |
select l.date,(select count(*)
from login l1
where (user_id,DATE_ADD(l1.date,INTERVAL 1 DAY)) in (select user_id,date from login )
and l1.date = l.date) as counts
from login l
group by date) as table2
- 由于除数不能为0，所以用case语句判断如果当天没有新用户登录，所以该日的新用户留存率也就为0。
select table1.date,
case table1.new_counts
when 0 then 0.000
else round(table2.counts / table1.new_counts, 3)
end as p
from
(select date,(select count(*)
from (select min(user_id),date
from login
group by user_id) as t
where t.date = l.date
) as new_counts
from login l
group by date
order by date) as table1
,
(select l.date,(select count(*)
from login l1
where (user_id,DATE_ADD(l1.date,INTERVAL 1 DAY)) in (select user_id,date from login )
and l1.date = l.date) as counts
from login l
group by date) as table2
where table1.date = table2.date
order by date;
查看8道真题和解析
