涉及单一和关联子查询，日期格式化等处理，排名top20%，滴滴真题、电商真题等。
13.红包领取情况
【题目】
“用户活跃表”记录了用户的登录信息，包括用户标识、用户登录日期，以及是否是新用户（如果是新注册的用户值为1；如果是老用户，值为0）。
“领取红包表”里记录了用户领取红包的信息，包括抢红包日期、抢红包时间、用户ID、领取红包金额。
现在业务部门需要分析出以下问题：
1.计算2019年6月1日至今，每日DAU（活跃用户是指有登陆的用户）
2.分析每天领取红包的用户数、人均领取金额、人均领取次数，要考虑用户属性及领取红包未登录情况。
3.分析每个月按领红包取天数为1、2、3……30、31天区分，计算取每个月领取红包的用户数，人均领取金额，人均领取次数。
4.分析每个月领过红包用户和未领红包用户的数量
-- 1.
select login_date, count(用户id)
from 用户活跃表
where login_date > 20190601
group by login_date;
-- 2.
select a.抢红包日期,
count(distinct a.用户id) 领取红包的用户数,
count(distinct (case when b.用户 = 1 then a.用户id else null end)) 领取红包的新用户数,
count(distinct (case when b.用户 = 0 then a.用户id else null end)) 领取红包的新用户数,
count(distinct (case when b.登录日期 is null then a.用户id else null end)) 领取红包的未登录用户数,
sum(a.领取红包金额) / count(distinct a.用户id),
count(a.*) / count(distinct a.用户id)
from 领取红包表 a
left join 用户活跃表 b
on a.用户id = b.用户id
and a.抢红包日期 = b.登录日期
group by a.抢红包日期;
-- 3.
select month(抢红包日期) as 月份,
count(distinct 抢红包日期) as 领取天数,
count(distinct 用户ID) as 每个月领取红包的用户数,
sum(金额)/count(distinct 用户ID), as 人均领取金额,
count(*)/count(distinct 用户ID) as 人均领取次数
from 领取红包表
group by month(抢红包日期);
-- 4.每个月领过红包用户和未领红包用户的数量
select count(t2.用户id) ,
count(t1.用户id) -
from
(
select month(抢红包日期) as 月份，distinct 用户id
from 用户活跃表
group by month(抢红包日期), 用户id
) t1
left join
(
select month(抢红包日期) as 月份，distinct 用户id
from 抢红包表
group by month(抢红包日期), 用户id
) t2
on t1.用户id = t2.用户id
and t1.月份 = t2.月份
group by t1.月份;
14.查找前20%
【题目】
用户访问次数表，列名包括用户编号、用户类型、访问量。要求在剔除访问次数前20%的用户后，每类用户的平均访问次数。（拼多多、网易面试题）
select 用户类型, avg(访问量)
from(
select *,
row_number()over(order by 访问量 desc) rk
from 用户访问次数表
) tmp
-- where rk > max(rk)*0.2 -- 错误原因：max()不是数值！需要select再做计算
where rk > (select max(rk) from tmp) * 0.2
group by 用户类型;
15.查找前三名
【题目】
“成绩表”记录了学生的学号，课程，成绩。为了对学生成绩进行考核，现需要查询每门课程的前3高成绩。注意：如果出现并列第一的情况，则同为第一名。
select 学号，课程，成绩
from
(
select *, dense_rank()over(partition by 课程 order by 成绩 desc) as rk
from 成绩表
) as tmp
where rk <=3
16.用户登录表
现有用户登录时间表——id，姓名，邮箱地址和用户最后登录时间。
问题：生成一张临时表（表名：用户登录表），表中呈现四列数据分别为：姓名，最后登录时间，个人登录时间排名row_number，个人登录日期排名（同一天登录多次算一次dense_rank）
考点：
- Row_number碰到相同数据时，排名按照记录集中记录的顺序依次递增
- Dense_rank：所有相同数据的排名都是一样的，并且连续。
- Rank：相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
select 姓名,最后登录时间,
row_number() over (partition by 姓名
order by 最后登录时间 asc) as 登录时间排名,
dense_rank() over (partition by 姓名
order by date_format( 最后登录时间,'%Y%m&d' ) asc) as 登录天数排名
from 用户登录表;
【题目】
下表是双十一客户购买清单表（客户id，购买时间，商品名称，金额）。双十一当天，商家为每位客户购买的第一个商品免单，请找出商品名称，客户姓名及购买金额。
select 客户id，商品名称，金额
(select *,
row_number() over(partition by 客户id order by 购买时间 asc) as rk
from 双十一客户购买清单表
where 购买时间 is "2020-11-11"
) tmp
where rk=1;
17.分组比较，子查询
现在有三个表，“学生表”，“课程表”，“成绩表”。
“学生表”记录了学生的基本信息，有“学号”、“姓名”、“出生日期”、“性别”。“成绩表”记录了学生选修课程的成绩，包括“学号”，选修的“课程号”以及对应课程的“成绩”。“课程表”记录了学生选修的课程信息，包括课程号、课程及其对应的“老师号”。“学生表”和“成绩表”通过“学号”联结，“成绩表”和“课程表”通过“课程编号”联结。
现在要查找出每门课程中成绩最好的学生的姓名和该学生的课程及成绩。
需要注意：可能出现并列第一的情况。
-- 解答1
select a.课程,b.学号,b.成绩,c.姓名
from 课程表 a
left join
(
select * , rank() over(partition by 课程号 order by 成绩 desc) as 排名
from 成绩表
) as b
on a.课程号 = b.课程号
left join 学生表 c
on b.学号 = c.学号
and b.排名=1;
-- 解答2
select b.课程,a.学号,a.成绩,c.姓名
from 成绩表 a
left join 课程表 b on a.课程号 = b.课程号
left join 学生表 c on a.学号 = c.学号
where (a.课程号, a.成绩) in ( -- 注意这种写法，比较陌生但好用
select 课程号, max(成绩)
from 课程表
group by 课程号
)
【举一反三】
有两个表，Employee 表用于记录员工的薪水和在哪个部门，包括员工的Id、员工的姓名（Name）、薪水（Salary） 和 员工所在部门Id（Department Id）。Department 表用于记录公司所有部门的信息，包括部门Id，部门名称（Name）。
1.找出每个部门工资最高的员工
2.检索工资大于部门平均工资的员工信息。
-- 1：单一值的子查询 ~in
select t1.名称, t2.员工姓名,t2.薪水
from Department t1
left join Employee t2
on t1.部门Id = t2.部门Id
where (t2.部门Id, t2.薪水) in (
select 部门Id, max(薪水)
from Employee
group by 部门Id
)
-- 2；关联子查询
select t1.员工姓名,t1.薪水
from Employee t1
where 薪水 >
(
select avg(薪水)
from Employee
where 部门Id = t1.部门Id -- 子查询和父查询用where关联
)
子查询：
-
多行
-
单一值：in any all between
-
在每个组里比较（关联子查询）
19.滴滴2020年笔试题
“订单信息表”里记录了巴西乘客使用打车软件的信息，包括订单id，乘客id，订单呼叫、接单、取消、完单时间。（滴滴2020年笔试题）
（1）表中的时间是北京时间，巴西比中国慢11小时。
（2）应答时间列的数据值如果是“1971”年，表示该订单没有司机应答，属于无效订单。
应答率 = 呼叫订单中被应答的比例
完单率= 呼叫订单中完单的比例
呼叫应答时间= 被应答订单的，从呼叫到应答的平均时长
问题
-
订单的应答率，完单率分别是多少？
-
呼叫应答时间有多长？
-
从这一周的数据来看，呼叫量最高的是哪一个小时（当地时间）？呼叫量最少的是哪一个小时（当地时间）？
-
呼叫订单第二天继续呼叫的比例有多少？
-
（选做）如果要对乘客进行分类，你认为需要参考哪一些因素？
日期处理函数总结
- cast+datetime：e.g. cast("2020-01-01 8:00" as datetime)，可以搭配update 表 set 新列 = cast(旧列 as 格式)
- date_format：日期数据格式化，如date_format(call_time,'%k') %k表示提取24小时制中的小时数值
- date_sub：从日期减去指定的时间间隔，如date_sub(date, interval 10 day) 表示减去10天之后
- timestampdiff：计算两个日期的差值，timestampdiff(unit, begin, end)，unit可以是second,minute, hour, day, week, month, year等等
- curdate():获取当前时间，取最近一周的数据用date>date_sub(curdate(), interval 7 day)
解答
-- 预处理
update 订单信息表
set 应答时间 = date_sub(cast("应答时间" as datetime),
interval -11 hour) ; -- 应答时间列处理为巴西时间，其他列同理
-- 1.应答率，完单率
select
sum(case when 应答时间=1970 then 0 else 1) / count(订单呼叫) as 应答率,
sum(case when 完单时间=1970 then 0 else 1) / count(订单呼叫) as 完单率
from 订单信息表;
-- 2.呼叫平均应答时间
select avg(timestampdiff(minute, 呼叫时间,应答时间)) as 呼叫平均应答时间
from 订单信息表
where 应答时间 <> 1970;
-- 3.呼叫量最高的是哪个小时（注意七天用datesub interval -11hour处理为巴西时间）
select date_format(呼叫时间,"%k") as 小时,
count(呼叫时间) as 每小时呼叫量
from 订单信息表
where 呼叫时间 > date_sub(curdate(), interval 7 day) -- 最近7天内
group by 小时
order by desc;
-- 4.第二天继续呼叫的比例
select t1.呼叫时间,
count(distinct t2.用户id) / count(distinct t1.用户id)
from 订单信息表 t1
left join 订单信息表 t2
on t1.用户id = t2.用户id
and timestampdiff(d,t1.呼叫时间, t2.呼叫时间) = 1
group by t1.呼叫时间
-- 注意不做SELECT DISTINCT直接JOIN会产生笛卡尔积
5.乘客分类方法
- 用户行为分类
1) 根据完成时间和接单时间，判断长途/中途/短途，分析乘车习惯。
2) 根据呼叫时间可以判断乘客是在时间点发单的，乘客需求是如何产生的，可分析用户在哪些场景有乘车需求，上班、下班、就餐、出游、临时等场景。
-
用户价值分类
利用RFM模型，具体定义：
R：最近一次乘客的完单时间。
F：乘客打车的频率。
M：打车消费的金额。此处可以用乘车过程消耗的时长来代替等。
20.电商销量分析
某公司在不同的电商平台上都有店铺，下面是该公司的销售数据表(每个表有一行示例数据)。略
问题1.对于指定品类号范围（品类号列表：12，33，45，99，1001），查询2019年每个电商平台上每个品牌号对应每个品类号的累计销售额
select a.品牌号,c.品牌名, a.品类号,b.品类名,a.电商平台,sum(销售额)
from 月销售统计表 a
left join 品类表 b
on a.品类号 = b.品类号
left join 品牌表 c
on a.品牌号 = c.品牌号
where 品类号 in (12, 33, 45, 99, 1001)
and year(月份)=2019;
group by a.电商平台, a.品牌号, a.品类号,a.电商平台
问题2:查询2019年有5个以上(含5个)不同品类号的单月单平台销售额大于等于10000的品牌列表，及对应的不同品类号数量
select 品牌名 count(distinct (case when 销售额 >= 10000 then 品类号 else null)) as 品类数
from 月销售统计表 a
left join 品牌表 b
on a.品牌号 = b.品牌号
where year(月份) = 2019
group by a.品牌号
having count(distinct (case when 销售额 >= 10000 then 品类号 else null)) >= 5;
问题3:查询2019年只在平台1上有销售额的品牌中，累计销售额最大的Top30个品牌及对应的销售额
-- 筛选放入子查询
select 品牌号, sum(销售额) as 总销售额
from 月销售统计表
where year(月份) = 2019
and 品牌号 in (
select 品牌号, sum(销售额) as 总销售额
from 月销售统计表
where year(月份) = 2019 and 平台 = "平台2"
group by 品牌号
having 总销售额=0
)
group by 品牌号
order by 总销售额 desc
limit 30;
问题4:查询2019年在两个电商平台中分别同时都能进入销售额Top 50的品牌及对应的全电商平台累计销售额
select 品牌号, sum(总销售额)
from(
select
平台,品牌号,总销售额
dense_rank() over(partition by 平台 order by 总销售额 desc) 销量排名
from(
select 品牌号, 平台, sum(销售额) as 总销售额
from 月销售统计表
where year(月份) = 2019
group by 品牌号,平台
) tmp
) tmp2
where 销量排名 <=50
group by 品牌号
having count(平台)=2;
21.滴滴业务统计
现有四张表，分别是“司机数据”表，“订单数据”表，“在线时长数据”表，“城市匹配数据”表。（滴滴面试题）
业务问题
- 分析出2020年8月各城市每天的司机数、快车订单量和快车流水数据。
select
c.城市名,
a.日期,
count(distinct a.司机id) as 司机数,
count(a.订单id) as 快车订单量,
sum(a.流水) as 快车流水数据
from 订单数据 a
inner join 司机数据 b on a.司机id = b.司机id
and a.日期 between "2020-08-01" and "2020-08-31"
and a.产品线id = 3
inner join 城市匹配数据 c on b.城市id = c.城市id
group by c.城市名, a.日期;
- 提取2020年8月和9月，每个月的北京市新老司机（首单日期在当月为新司机）的司机数、在线时长和TPH（订单量/在线时长）数据。
-- （看错题意了）group by 新老司机
select 月份, 新老司机,
count(新老司机),
sum(在线时长),
sum(订单量) / sum(在线时长）
(
select month(b.订单日期) as 月份,a.司机id,b.订单量,c.在线时长
（case
when month(a.首单日期) = month(b.订单日期) then "新司机"
when month(a.首单日期) < month(b.订单日期) then "老司机"
else null
end) as "新老司机"
from 司机数据 a
left join
(
select month(订单日期) as 月份, 司机id, count(订单id) as 订单量
from 订单数据
where (订单日期 between "2020-08-01" and "2020-09-30")
group by month(订单日期), 司机id
) b
on a.司机id = b.司机id
on a.月份 = b.月份
and a.城市id = 1000000 -- 北京市
left join
（
select month(订单日期) as 月份, 司机id, count(在线时长) as 在线时长
from 订单数据
where 订单日期 between "2020-08-01" and "2020-09-30"
group by month(订单日期), 司机id
） c
on a.司机id = c.司机id
and a.月份 = c.月份
) tmp
group by 月份, 新老司机;
-- 标准解答，t1计算新老司机数，t2计算在线时间, t3计算tph，然后join
select
t1.*,t2.订单量,t3.在线时长,(t2.订单量/t3.在线时长) TPH
from
(
select
t.城市id,
month(t.日期) 月份,
sum(if(新老司机='新司机',1,0)) 新司机数,
sum(if(新老司机='老司机',1,0)) 老司机数
from
(
select
a.*,
if(date_format(日期,'%y-%m')=DATE_FORMAT(首次完成订单时间,'%y-%m'),'新司机','老司机') 新老司机
from 司机数据 a
) t
where (t.日期 BETWEEN '2020-08-01' and '2020-09-30' ) and t.城市id=100000
group by month(t.日期)
) t1
left join
(
select
b.城市id,
month(b.日期) 月份,
count(a.订单id) 订单量
from 订单数据 a
left join 司机数据 b
on a.司机id=b.司机id
where (b.日期 BETWEEN '2020-08-01' and '2020-09-30') and b.城市id=100000
group by month(a.日期)
) t2
on t1.城市id=t2.城市id and t1.月份=t2.月份
left join
(
select
b.城市id,
month(b.日期) 月份,
sum(a.在线时长) 在线时长
from 在线时长数据 a
left join 司机数据 b
on a.司机id=b.司机id
where (b.日期 BETWEEN '2020-08-01' and '2020-09-30') and b.城市id=100000
group by month(a.日期)
) t3
on t1.城市id=t3.城市id and t1.月份=t3.月份
- 分别提取司机数大于20，司机总在线时长大于2小时，订单量大于1，乘客数大于1的城市名称。
-- 计算每个城市司机数和总在线时长和订单量和乘客数
select t1.城市名称
from 城市匹配数据 t1
left join
(
select a.城市id,
count(distinct a.司机id) 司机数,
sum(c.在线时长) 司机总在线时长,
count(b.订单id) 订单量,
count(distinct a.乘客id) 乘客数
from 司机数据 a
inner join 订单数据 b
on a.司机id=b.司机id
inner join 在线时长 c
on c.司机id=c.司机id
group by a.城市id
having count(distinct a.司机id)>20 and sum(c.在线时长)>2 and count(b.订单id) >1 and count(distinct a.乘客id) >1
) t2
on t1.城市id = t2.城市id;