题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
https://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
//获得日期
select date from
login
GROUP BY date
//根据日期查询user_id
SELECT DISTINCT user_id
from LOgin
where date=(select date from
login
GROUP BY date)
//查询每个日期用户次日登录的用户id(包括新用户)
SELECT a.user_id,a.date,b.user_id,b.date
from login a
inner join
login b
on DAY(a.date)+1=DAY(b.date)
where a.user_id=b.user_id
//查询每个新用户登录的日期
select user_id,min(date)
from login
group by user_id;
//新用户次日继续登录的user_id
SELECT a.* FROM
(SELECT a.user_id,a.date
from login a
inner join
login b
on DAY(a.date)+1=DAY(b.date)
where a.user_id=b.user_id) a
INNER JOIN
(select user_id,min(date) date
from login
group by user_id) b
on a.date=b.date and a.user_id=b.user_id
//查询每天的新用户数目
SELECT count(*),date from
(select user_id,min(date) date
from login
group by user_id) a
group by a.date
//查询每天新用户第二天登录的人数
SELECT count(*) count,date FROM
(SELECT a.* FROM
(SELECT a.user_id,a.date
from login a
inner join
login b
on DAY(a.date)+1=DAY(b.date)
where a.user_id=b.user_id) a
INNER JOIN
(select user_id,min(date) date
from login
group by user_id) b
on a.date=b.date and a.user_id=b.user_id) a
group by a.date
//
SELECT a.date,IFNULL(b.count,0) count from
(select date from
login
GROUP BY date) a
left join
(SELECT count(*) count,date from
(select user_id,min(date) date
from login
group by user_id) a
group by a.date) b
on a.date=b.date
SELECT a.date,IFNULL(b.count,0) count from
(select date from
login
GROUP BY date) a
left join
(SELECT count(*) count,date FROM
(SELECT a.* FROM
(SELECT a.user_id,a.date
from login a
inner join
login b
on DAY(a.date)+1=DAY(b.date)
where a.user_id=b.user_id) a
INNER JOIN
(select user_id,min(date) date
from login
group by user_id) b
on a.date=b.date and a.user_id=b.user_id) a
group by a.date) b
on a.date=b.date
SELECT a.date,ROUND(IFNULL(a.count/b.count,0),3) p from
(SELECT a.date,IFNULL(b.count,0) count from
(select date from
login
GROUP BY date) a
left join
(SELECT count(*) count,date FROM
(SELECT a.* FROM
(SELECT a.user_id,a.date
from login a
inner join
login b
on DAY(a.date)+1=DAY(b.date)
where a.user_id=b.user_id) a
INNER JOIN
(select user_id,min(date) date
from login
group by user_id) b
on a.date=b.date and a.user_id=b.user_id) a
group by a.date) b
on a.date=b.date) a
INNER JOIN
(SELECT a.date,IFNULL(b.count,0) count from
(select date from
login
GROUP BY date) a
left join
(SELECT count(*) count,date from
(select user_id,min(date) date
from login
group by user_id) a
group by a.date) b
on a.date=b.date) b
on a.date=b.date