30道经典SQL面试题讲解(21-30)
本篇节选自书籍《对比Excel，轻松学习SQL数据分析》一书，主要讲解数据分析面试中常见的30道SQL面试题。
1-20道可以看：
21 获取新增用户数
现在有一个用户表user_reg_table，这张表存储了每位用户的uid(用户id)、reg_time(注册时间)等其他信息，我们想知道某一天的新增用户数，以及该天对应的过去7天内每天平均新增用户数，该怎么实现呢？
user_reg_table表如下所示：
|
uid |
reg_time |
|---|---|
|
1 |
2019/12/25 10:00:00 |
|
2 |
2019/12/26 10:00:00 |
|
3 |
2019/12/27 10:00:00 |
|
4 |
2019/12/28 10:00:00 |
|
5 |
2019/12/29 10:00:00 |
|
6 |
2019/12/30 10:00:00 |
|
7 |
2019/12/31 10:00:00 |
|
8 |
2020/1/1 10:00:00 |
|
9 |
2020/1/2 10:00:00 |
|
10 |
2020/1/3 10:00:00 |
|
11 |
2020/1/4 10:00:00 |
自己先想一下代码怎么写，然后再参考我的代码。
set @day_date = "2020-01-01";
select
count(if(date(reg_time) = @day_date,uid,null)) as new_cnt
,count(uid)/7 as 7_avg_cnt
demo.user_reg_table
where
date(reg_time) between date_sub(@day_date,interval 6 day) and @day_date解题思路：
我们是想知道某一天的用户数，这个某一天是一个可变的值，所以我们想到了变量，通过设置变量来达到日期的变化；其次我们还需要过去7天，在变量的基础上减去6天即可，这里面需要注意的是，我们用的between用来筛选介于过去7天和今天之间的用户，而不能直接使用大于7天前日期的这个条件，因为大于7天前的日期很有可能包括你设置的变量后面的日期。最后运行结果如下：
|
new_cnt |
7_avg_cnt |
|---|---|
|
1 |
1 |
22 获取用户首次购买时间
现在我们有一张表first_order_table，这张表中包含了order_id(订单id)、uid(用户id)、order_time(订单时间)，我们想知道每个用户的首次购买时间，以及是否在最近7天内，该怎么实现呢？
first_order_table表如下所示：
|
order_id |
uid |
order_time |
|---|---|---|
|
201901 |
1 |
2020/1/1 10:00:00 |
|
201902 |
2 |
2020/1/2 10:00:00 |
|
201903 |
3 |
2020/1/3 10:00:00 |
|
201904 |
1 |
2020/1/4 10:00:00 |
|
201905 |
2 |
2020/1/5 10:00:00 |
|
201906 |
3 |
2020/1/6 10:00:00 |
|
201907 |
1 |
2020/1/7 10:00:00 |
|
201908 |
2 |
2020/1/8 10:00:00 |
|
201909 |
3 |
2020/1/9 10:00:00 |
|
201910 |
1 |
2020/1/10 10:00:00 |
|
201911 |
2 |
2020/1/11 10:00:00 |
自己先想一下代码怎么写，然后再参考我的代码。
select
t1.uid
,t1.first_time
,(date(t1.first_time) > date_sub(curdate(),interval 6 day)) is_7_day
(select
,min(order_time) first_time
demo.first_order_table
group by
)t1解题思路：
我们主要有两个事情，第一件事就是获取每个用户的首次购买时间，其实就是最小时间，然后再对最小时间和最近7天进行比较，得出首次购买时间是否在最近7天。最后运行结果如下：
|
uid |
first_time |
is_7_day |
|---|---|---|
|
1 |
2020-01-01 10:00:00 |
0 |
|
2 |
2020-01-02 10:00:00 |
0 |
|
3 |
2020-01-03 10:00:00 |
0 |
23 同时获取用户和订单数据
还是前面的两张表user_reg_table和first_order_table，现在我们想知道过去7天每天的新增用户数、订单数、下单用户数，该怎么实现呢？
自己先想一下代码怎么写，然后再参考我的代码。
set @day_date = "2020-01-04";
select
t1.tdate
,t1.new_cnt
,t2.order_cnt
,t2.uid_cnt
select
date(reg_time) tdate
,count(uid) new_cnt
demo.user_reg_table
where
date(reg_time) between date_sub(@day_date,interval 6 day) and @day_date
group by
date(reg_time)
left join
select
date(order_time) tdate
,count(order_id) order_cnt
,count(distinct uid) uid_cnt
demo.first_order_table
where
date(order_time) between date_sub(@day_date,interval 6 day) and @day_date
group by
date(order_time)
on t1.tdate = t2.tdate解题思路：
我们要获取每天的新增用户数以及订单数，新增用户数和订单数据是存储在两个不同的表中，所以我们可以先分别获取每天的新增用户数和每天的订单数，然后再根据日期把两个表拼接在一起。最后运行结果如下：
|
tdate |
new_cnt |
order_cnt |
uid_cnt |
|---|---|---|---|
|
2019-12-29 |
1 |
null |
null |
|
2019-12-30 |
1 |
null |
null |
|
2019-12-31 |
1 |
null |
null |
|
2020-01-01 |
1 |
1 |
1 |
|
2020-01-02 |
1 |
1 |
1 |
|
2020-01-03 |
1 |
1 |
1 |
|
2020-01-04 |
1 |
1 |
1 |
24 随机抽样
还是前面的两张表user_reg_table和first_order_table，现在我们想要从用户表中随机抽取5位用户，以及这5位用户的历史购买订单数，想想该怎么实现呢？
自己先想一下代码怎么写，然后再参考我的代码。
select
user_table.uid
,t.order_cnt
demo.user_reg_table user_table
left join
select
,count(order_id) as order_cnt
demo.first_order_table
group by
on user_table.uid = t.uid
order by rand()
limit 5解题思路：
我们要随机获取5位用户的历史购买订单数，首先需要生成每个用户历史的购买订单数，然后再从中随机抽取5位。具体的随机抽取规则为：利用rand()生成随机数，然后再利用order by进行排序，最后利用limit把前5条显示出来。最后运行结果如下：
|
uid |
order_cnt |
|---|---|
|
9 |
null |
|
3 |
3 |
|
8 |
null |
|
5 |
null |
|
11 |
null |
25 获取沉默用户数
还是前面的两张表user_reg_table和first_order_table，现在我们想获取沉默用户的数量，沉默的定义是已注册但是最近30天内没有购买记录的人，该怎么实现呢？
自己先想一下代码怎么写，然后再参考我的代码。
select
count(user_table.uid) chenmo_cnt
demo.user_reg_table user_table
left join
select
demo.first_order_table
where
date(order_time) < date_sub(curdate(),interval 29 day)
group by
on user_table.uid = t.uid
where
t.uid is null解题思路：
我们要获取近30天没有购买记录的人，可以先把最近30天内有购买记录的人取出来，然后用user_table表中的uid去拼接最近30天有购买记录的人，如果不能拼接到，即拼接结果为null，就表示这部分人最近30天没有购买。把null的部分取出来，然后对uid进行计数即可。最后运行结果为14，因为我们是用的curdate()，所以不同时间运行得到的结果会是不一样的。
26 获取新用户的订单数
还是前面的两张表user_reg_table和first_order_table，现在我们想获取最近7天注册新用户在最近7天内的订单数是多少，该怎么实现呢？
自己先想一下代码怎么写，然后再参考我的代码。
select
sum(t2.order_cnt)
select
demo.user_reg_table
where
date(reg_time) > date_sub(curdate(),interval 6 day)
left join
select
,count(order_id) order_cnt
demo.first_order_table
where
date(order_time) > date_sub(curdate(),interval 6 day)
group by
on t1.uid = t2.uid解题思路：
我们要获取最近7天注册新用户在最近7天内的订单数，首先获取最近7天新注册的用户，然后获取每个用户在最近7天内的订单数，最后将两个表进行拼接，且新用户表为主表，进行左连接。最后运行结果为14，不同时间运行得到的结果会是不一样的。
27 获取借款到期名单
现在有一张借款表loan_table,这张表记录了每笔借款的id、loan_time(借款时间)、expire_time(到期时间)、reback_time(还款时间)、amount(金额)、status(还款状态,1表示已还款、0表示未还款)，我们想要获取每天到期的借款笔数、借款金额和平均借款天数，该怎么实现呢？
loan_table表如下所示：
|
id |
loan_time |
expire_time |
reback_time |
amount |
status |
|---|---|---|---|---|---|
|
1 |
2019/12/1 |
2019/12/31 |
|
2208 |
0 |
|
2 |
2019/12/1 |
2019/12/31 |
2019/12/31 |
5283 |
1 |
|
3 |
2019/12/5 |
2020/1/4 |
|
5397 |
0 |
|
4 |
2019/12/5 |
2020/1/4 |
|
4506 |
0 |
|
5 |
2019/12/10 |
2020/1/9 |
|
3244 |
0 |
|
6 |
2019/12/10 |
2020/1/9 |
2020/1/12 |
4541 |
1 |
|
7 |
2020/1/1 |
2020/1/31 |
2020/1/10 |
3580 |
1 |
|
8 |
2020/1/1 |
2020/1/31 |
|
7045 |
0 |
|
9 |
2020/1/5 |
2020/2/4 |
|
2067 |
0 |
|
10 |
2020/1/5 |
2020/2/4 |
|
7225 |
0 |
自己先想一下代码怎么写，然后再参考我的代码。
select
count(id) as loan_cnt
,sum(amount) as loan_amount
,avg(datediff(reback_time,loan_time)) avg_day
demo.loan_table
where
expire_time = curdate()解题思路：
我们是要获取每天到期的数据，只需要通过筛选到期时间等于当天把当天到期的数据筛选出来，然后对id进行计数得到到期笔数，对amount进行求和得到到期金额，对还款时间和借款时间做差取平均得到平均借款天数，注意这里是用的还款时间和借款时间做差，而非到期时间和借款时间做差，因为有可能提前还款或逾期。最后运行结果为空，表示今天没有到期的借款。
28 获取即将到期的借款信息
还是前面的借款表loan_table，现在我们想知道有多少笔借款会在未来7天内到期，其中有多少笔是已经还款的，该怎么实现呢？
自己先想一下代码怎么写，然后再参考我的代码。
select
count(id) as loan_cnt
,count(if(status = 1,id,null)) as reback_cnt
demo.loan_table
where
expire_time between curdate() and date_sub(curdate(),interval 6 day)解题思路：
我们是要获取未来7天内要到期的借款笔数和其中已经还款的笔数，首先把最近7天内要到期的数据筛选出来，然后再通过还款状态status进行判断，再获取已还款的笔数。最后运行结果为空。
29 获取历史逾期借款信息
还是前面的借款表loan_table，现在我们想知道历史逾期的笔数和金额以及至今还逾期的笔数和金额，该怎么实现呢？
自己先想一下代码怎么写，然后再参考我的代码。
select
count(id) as loan_cnt
,sum(amount) as loan_amount
,count(if(status = 0,id,null)) as no_reback_cnt
,sum(if(status = 0,amount,0)) as no_reback_amount
demo.loan_table
where
(reback_time > expire_time)
or (reback_time is null and expire_time < curdate())解题思路：这里面的关键信息在于逾期怎么判断，逾期是用到期时间和还款时间去进行比较，如果是逾期且现在已经还款了的，可以直接比较到期时间和还款时间，如果还款时间大于到期时间，说明是逾期的；还有一种是逾期且现在还未还款的，这种情况是没有还款时间的，也就是还款时间是空，但是到期时间是在今天之前，说明已到期但是未还款。最后运行结果如下：
|
loan_cnt |
loan_amount |
no_reback_cnt |
no_reback_amount |
|---|---|---|---|
|
5 |
19896 |
4 |
15355 |
30 综合实战
这一题是我们最后一道实战题，给大家还原一下我们在前面梳理数据库逻辑的时候遇到的情况。假如你现在刚入职一家新的电商公司，你需要通过一个Sql把电商整个漏斗转化环节的数据全部取出来：主要当日总浏览量、浏览人数、加购物车数、加购物车人数、订单数、下单人数、确认收货订单数，该怎么写。已知有如下几张表：
browse_log_table(浏览记录表)：id(浏览id)、product_id(商品id)、uid(用户id)、channel(渠道)、browse_time(浏览时间)......；
cart_table(购物车详情表)：id(购物车id)、browse_id(浏览id)、cart_time(加购物车时间)......；
order_table(订单详情表)：id(订单id)、cart_id(购物车id)、order_time(订单时间)、amount(订单金额)......；
take_table(收货详情表)：order_id(订单id)、take_time(确认收货时间)......。
select
count(browse_log_table.id) as browse_cnt
,count(distinct browse_log_table.uid) as browse_uid_cnt
,count(cart_table.id) as cart_cnt
,count(distinct if(cart_table.id is not null,browse_log_table.uid,null)) as cart_uid_cnt
,count(order_table.id) as order_cnt
,count(distinct if(order_table.id is not null,browse_log_table.uid,null)) as order_uid_cnt
,count(take_table.id) as take_cnt
,count(distinct if(take_table.id is not null,browse_log_table.uid,null)) as take_uid_cnt
browse_log_table
left join
cart_table
on browse_log_table.id = cart_table.browse_id
left join
order_table
on cart_table.id = order_table.cart_id
left join
take_table
on order_table.id = take_table.order_id
where
browse_log_table.browse_time = curdate()想进一步了解更多内容的同学，可以阅读《对比Excel，轻松学习SQL数据分析》一书。
▊《对比Excel，轻松学习SQL数据分析》
张俊红 著
学习SQL 的主要原因是工作需要。网上关于数据相关岗位的招聘都要求有熟练使用SQL 这一条，为什么会这样呢？这是因为我们负责的是与数据相关的工作，而获取数据是我们工作的第一步，比如，你要通过数据做决策，但是现在公司的数据基本上不存储在本地Excel 表中，而是存储在数据库中，想要从数据库中获取数据就需要使用SQL，所以熟练使用SQL 成了数据相关从业者入职的必要条件。本书的所有代码和函数均以MySQL 8.0 为主。
（扫码了解本书详情）
如果喜欢本文欢迎 在看丨留言丨分享至朋友圈 三连
数据营销“教父”宋星十年倾心之作
如何通过XMind 实践OKR 工作法