数据分析/数据科学常见SQL题目：连续登录用户、留存率、最大观看人数

1. SQL的执行顺序是什么？on和join谁先执行，为什么？on和where的区别？

• 执行顺序：from→on→join→where→group by→having→select→distinct→order by→limit

为什么on在join前面执行？因为要先经过筛选，才能通过连接把外部行加进来。

on和where的区别：on筛选之后，可以通过外连接把移除的行再次添加进来，而where是最终过滤的作用。

举个例子 有一个学生表：班级、姓名；一个成绩表：姓名、成绩。我们需要返回X班的所有学生的成绩，但是班上有人缺考，也就是成绩表里没有姓名，我们先用on得到的就是有考试成绩的名字，通过外连接，我们就可以得到全班人的名字以及成绩。

2. 已知表user,字段id, date，求新用户的次日留存率

select a.date, count(distinct b.id)/count(distinct a.id) rate 
from (select id,min(date) date from user group by id) a	#求出用户的第一次登录时间
left join user b on a.id=b.id 
and datediff(b.date,a.date)=1
group by a.date

解析：首先需要找到每一个新用户的登录时间，与第二天进行对比，distinct是为了排除用户同一天多次登录的情况。（本题未考虑新增用户为0的情况，实际需不需要考虑由具体情况定）

3. 已知表user，字段id，date，求每个日期新用户的次日留存率

select c.date,ifnull(d.rate,0) rate from
(select distinct(date) date from user) c
left join
(select a.date,count(distinct b.id)/count(distinct a.id) rate 
from (select id,min(date) date from user group by id) a
# 求出用户的第一次登录时间
left join user b on a.id=b.id and datediff(b.date,a.date)=1
group by a.date) d
on c.date=d.date

d表内容和上一题一样，但我们需要考虑某天新增用户为0的情况，并输出新增用户的次日留存率0以及日期。通过c表进行左连接之后，日期为连接条件，那么我们就获得了每个日期以及对应的新用户次日留存率。因为d表中不含新增用户为0的留存率情况，因此左连接之后，这种情况就会变成null值，所以通过ifnull函数转化成0，这样就输出了每个日期新用户的次日留存率情况了。

4. 已知表a，字段id，log_date，求连续登录天数大于等于2的用户id

SELECT distinct id 
FROM (select *,dense_rank() over (PARTITION by id ORDER BY log_date) 
dk from a) t
GROUP BY id,DATE_SUB(log_date,INTERVAL dk day)
HAVING count(DISTINCT(log_date))>=2 

解析：首先需要给用户的登录日期排序，只能用dense_rank是因为用户同一天可能会登录多次。如果一个用户连续登录或者同一天多次登录，那么他的登录日期与序号的差值是相同的，所以可以根据id,差值分组，便可以求得用户的连续登录和同一天多次登录的累计天数。 having里面的distinct是为了排除用户同一天多次登录的情况，select里面的distinct是为了排除一个用户多次连续登录，比如：连续登录两天，第三天未登录，接着又连续登录两天。

5. 已知表table，字段id，date，求连续三天或三天以上未登录的用户id

select  distinct id 
from (select *,lag(date) over(partition by id order by date) date1 from table)a
where datediff(date,date1)>=4

lag函数是为了获得用户上一次登录的时间，利用where与本次登录时间进行比较。如果一个用户要连续三天没登录，因此他下一次登录就应该在第四天，所以是大于等于4。

6. 订单表orders，字段有order_id，user_id，date。求22年1月每个用户单天订单最高次数和每个用户单天订单最高次数的日期。

select user_id,date,cnt 
from 
(select user_id,date,cnt,rank() over(partition by user_id order by cnt desc) rk 
from (select user_id,date,count(order_id) cnt from orders where date_format(date,'%Y%m')=202201 group by user_id,date)a
)b
where rk=1

解析：每个用户每天可能下单多次，因此需要先统计每个用户每天的订单量也就是a表。然后利用窗口函数对用户id分组对订单量倒序排序，筛选出排名为1的数据就可以了。如果题目不要求求出每个用户单天订单最高次数的日期，那么可以直接对a表用户id分组,使用max函数再得出结果了。

7. 已知：商品上下架时间表log，商品id，上下架类型type（online：上架，offline：下架），时间log_time，输出每个商品id，上架时间online_time，下架时间offline_time。

 select a.id,a.log_time online_time,b.log_time offline_time from (select id,log_time,row_number()over(partition by id order by log_time) rn1 from log where type='online')a
left join (select id,log_time,row_number()over(partition by id order by log_time) rn2 from log where type='offline')b
on a.id=b.id and a.rn1=b.rn2

解析：同一个商品有多个上下架时间,因此不能通过case when进行行转列。首先需要对同一个商品所有的上下架时间进行排序，根据序号让每一个上架时间匹配对应的下架时间。用left join是因为会存在商品有上架时间，没有下架时间这种情况。

8. 已知用户行为日志表tb_user_log，uid用户id，artical_id文章id，in_time观看开始时间，out_time观看结束时间。统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少。结果按最大人数降序。

select artical_id,max(uv) max_uv from 
(select artical_id,sum(num) over(partition by artical_id order by dt,num desc) uv from (select artical_id,in_time dt,1 num from tb_user_logunion all select artical_id,out_time dt,-1 num from tb_user_log) a 
) b 
group by artical_id
order by max_uv desc

首先需要想到的是，做这种类似某一时刻最大在线人数的题目，都可以转化成当用户开始观看时用户数+1，当用户结束观看时用户数-1，然后通过sum窗口函数累计求和。如果能想到这一点，这题就很简单了。所以刚开始需要将用户观看开始、观看结束的时间表取出来，再合并变成表a。通过sum窗口函数对每个文章进行分组根据时间顺序排序、num倒序排序累计求和变成表b，用sum函数是因为需要求出每个时刻的累计在线人数。num倒序排序是因为同一时刻有进入也有离开时，先记录增加，再记录减少。最后对b表通过文章id分组，max函数就可以求到最大在看人数了，结果按降序排序，加个order by max_uv desc就行了。

9. 有一张销量信息表table1，字段有类目cate，店铺id，销售额sales。求出每个类目销售额排名前40%的店铺信息。（输出字段cate，id，sales）

select cate,id,sales from (
select cate,id,sales,row_number() 
over(partition by cate order by sales desc) rn,
count(id) over(partition by cate) ct from table1
) a 
where rn/ct<=0.4

解析：首先先对每个类目的销售额进行倒序排序，也就是row_number()窗口函数。count(id) over（partition by cate）是求出每个类目中的店铺个数，然后从a表中查询设置where条件前40%就可以了。