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