牛客SQL刷题记录

SQL70 牛客每个人最近的登录日期(五)

step1: 求出每个用户的最早登录时间，也就是定位出新用户，这个自查询定义为a

step2: 求出a表和login表的left join, 并且用date_diff定位出哪些人是次日登录了的

step3: 最后 再用login表中的date（包括新用户和次日登录的date）去left join，求出a表用户和b表用户的比值，那么就是次日留存率，使用ifnull处理出现null的情况

select c.date, ifnull(round(count(distinct b.user_id)/(count(a.user_id)),3),0) from 
(select date from login group by date) c left join 
-- 求出每个用户第一次登录（新用户）
(select user_id, min(date) as date from login group by user_id) a 
on a.date = c.date
-- 再去left join login表, 看看a表中有哪些人次日是登录了的
left join login as b
on a.user_id = b.user_id and datediff(a.date, b.date) = -1  
group by c.date

SQL68 牛客每个人最近的登录日期(三)

牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表

step1:求每个用户login时间的偏移，由于是次日留存，所以偏移一天。

select temp.user_id, temp.date_plus_1 from 
(select a.user_id, date_add(a.date, interval 1 day) as date_plus_1 from login a) temp
join login on login.user_id=temp.user_id and login.date=temp.date_plus_1

step2:随后利用login表中的user_id 和 temp表中的数据做除法求留存率，记得round保留3位小数。

select round(count(distinct t.user_id)/count(distinct login.user_id),3) as p from 
(select temp.user_id, temp.date_plus_1 from 
(select a.user_id, date_add(a.date, interval 1 day) as date_plus_1 from login a) temp
join login on login.user_id=temp.user_id and login.date=temp.date_plus_1 ) t, login 

SQL69 牛客每个人最近的登录日期(四)

step1: 求每个用户最早的一次登录记录，其实就是新用户记录了

select user_id, min(date) as 1st_login from login group by user_id

step2：这个时候直接求count(user_id) 会出现一个问题， 无新登录用户的date是没有办法显示0的，只能是null，不符合题意，所以要再join一下

select a.date, count(t.user_id) as new from (select distinct date from login) a 
left join (
-- 这个时候无新登录用户的date是没有办法显示0的，只能显示null，所以要再join一下
-- 求每个用户最早的一次登录记录，其实就是新用户记录了
select user_id, min(date) as 1st_login from login group by user_id) t on a.date = t.1st_login
group by a.date order by a.date