30道经典SQL面试题讲解(21-30)

本篇节选自书籍《对比Excel，轻松学习SQL数据分析》一书，主要讲解数据分析面试中常见的30道SQL面试题。

1-20道可以看：

30道经典SQL面试题讲解(1-10)

30道经典SQL面试题讲解(11-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
from 
    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
from
    (select
        uid
        ,min(order_time) first_time
    from
        demo.first_order_table
    group by
        uid
    )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
from
    (
    select
        date(reg_time) tdate
        ,count(uid) new_cnt
    from
        demo.user_reg_table
    where
        date(reg_time) between date_sub(@day_date,interval 6 day) and @day_date
    group by 
        date(reg_time)
    )t1
left join
    (
    select
        date(order_time) tdate
        ,count(order_id) order_cnt
        ,count(distinct uid) uid_cnt
    from
        demo.first_order_table
    where
        date(order_time) between date_sub(@day_date,interval 6 day) and @day_date
    group by 
        date(order_time)
    )t2
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
from
    demo.user_reg_table user_table
left join
    (
    select
        uid
        ,count(order_id) as order_cnt
    from
        demo.first_order_table
    group by
        uid
    )t
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
from
    demo.user_reg_table user_table
left join
    (
    select
        uid
    from
        demo.first_order_table 
    where
        date(order_time) < date_sub(curdate(),interval 29 day)
    group by
        uid
    )t
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)
from
    (
    select
        uid
    from
        demo.user_reg_table
    where
        date(reg_time) > date_sub(curdate(),interval 6 day)
    )t1
left join
    (
    select
        uid
        ,count(order_id) order_cnt
    from
        demo.first_order_table
    where
        date(order_time) > date_sub(curdate(),interval 6 day)
    group by
        uid
    )t2
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
from
    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
from
    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
from
    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
from
    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()

点分享

点收藏

点点赞

点在看