前四篇文章我们学习了
SQL校招社招刷题系列之投资信息查询
SQL校招社招刷题系列之学生成绩信息查询
SQL校招社招刷题系列之交叉表的制作
SQL校招社招刷题系列之比赛排列组合
源表
现有两张表,分别为register和login
login登录表
id | login_time | |
10001 | 2019/6/1 16:26 | |
10001 | 2019/6/2 9:26 | |
10001 | 2019/6/2 15:41 | |
10001 | 2019/6/3 17:16 | |
10001 | 2019/6/4 9:10 | |
10002 | 2019/6/2 15:30 | |
10003 | 2019/6/3 11:29 | |
10004 | 2019/6/3 21:35 | |
10004 | 2019/6/4 21:35 | |
10005 | 2019/6/4 22:36 | |
10006 | 2019/6/4 22:30 | |
id | register_time |
10001 | 2019/6/1 12:23 |
10002 | 2019/6/1 13:40 |
10003 | 2019/6/2 9:19 |
10004 | 2019/6/2 22:45 |
10005 | 2019/6/2 23:50 |
10006 | 2019/6/3 11:40 |
解题
题目描述
用SQL求出次日留存率(前一天注册的用户在第二天登录的比例)
题解
SELECT
d.num2/c.num1 AS rate
,d.date2 AS '日期'
FROM
(SELECT
COUNT(DISTINCT id) AS num1
,DATE(register_time) date1
FROM xiaoliu.register
GROUP BY DATE(register_time)) c
INNER JOIN
(SELECT
COUNT(DISTINCT a.id) num2
,DATE(b.login_time) date2
FROM
xiaoliu.register a
INNER JOIN
xiaoliu.login b
ON DATE(date_add(a.register_time,INTERVAL 1 DAY))=DATE(b.login_time)
AND a.id=b.id
GROUP BY DATE(b.login_time)) d
ON date_add(c.date1,INTERVAL 1 DAY)=d.date2;