薅羊毛记录仪

旧岁舍人如木落 新春予义再易风

文章目录 站点概览

shuyi

83 文章
11 分类
11 标签

sql面试题系列（一）

发布于 2021-02-18 | 分类于 SQL | 13分钟 | 2824字数 | 浏览量：

5~12题，涉及连续登录、行列互换、多表链接、留存率分析等

5.查找“不在表里的数据”

【题目】

下面是学生的名单，表名为“学生表”；近视学生的名单，表名为“近视学生表”。请问不是近视眼的学生都有谁？

select t1.id, t1.name
from xueshengbiao t1
left join jinshibiao t2
on t1.id = t2.id 
where t2.id is null;

6.薪水涨幅升序

【题目】

“雇员表“中记录了员工的信息，“薪水表“中记录了对应员工发放的薪水。两表通过“雇员编号”关联。查找当前所有雇员入职以来的薪水涨幅，给出雇员编号以及其对应的薪水涨幅，并按照薪水涨幅进行升序。
（注：薪水表中结束日期为2004-01-01的才是当前员工，否则是已离职员工）

-- 1.我的回答
select a.*, ifnull(b. 涨薪,0) as 涨薪金额
from guyuan a
left join (
select t1.id, t1.endtime, t2.endtime as 离职时间, t1.salary-t2.salary as 涨薪
from xinshui t1, xinshui t2
where t1.id = t2.id
	and t2.starttime = t1.endtime
) b
on a.id = b.id
where b.离职时间="2004-01-01"
order by b.涨薪金额;
-- 问题在于如果涨薪不止一次，就会出现bug

-- 2.标准答案，要限定原始薪水取入职时的数值
select t3.*, t1.当前薪水-t2.入职薪水 as 涨薪金额
from(
	select id, salary as 当前薪水
	from 薪水表
	where 结束日期="2004-01-01"
) t1  
left join 
(
	select a.*，b.salary as 入职薪水
	from 雇员表 a,  
	left join 薪水表 b  
	on a.id = b.id -- 复合连接条件
	and a.雇佣日期=b.起始时间
) t2
on t1.id = t2.id
left join 雇员表 t3
on t1.id = t3.id
order by 涨薪金额 desc;

7.日期对比，交叉连接

下面是某公司每天的营业额，表名为“日销”。“日期”这一列的数据类型是日期类型（date）.请找出所有比前一天（昨天）营业额更高的数据。（前一天的意思，如果“当天”是1月，“昨天”（前一天）就是1号）.

-- 我的答案，隐式连接
select t2.*
from 日销表 t1, 日销表 t2
where t1.id = t2.id,
and t2.date - t1.date = 1
and t2. revenue > t1. revenue

-- 标准答案，交叉连接（返回所有两两组合）
select t2.*
from 日销表 t1 
cross join 日销表 t2
on datediff(t2.date, t1.date) = 1
where t2.revenue > t1.revenue;

8.最小的n个数

【滴滴2020年面试题】“学生表”里记录了学生的学号、入学时间等信息。“成绩表”里是学生选课成绩的信息。两个表中的学号一一对应。

现在需要：

  1. 筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）

  2. 统计每个班同学各科成绩平均分大于80分的人数和人数占比

-- 第一问 easy
select 姓名, 年龄
from 学生表
where 入学时间 = "2017"
and 专业 = "计算机"
order by 年龄 asc  -- asc desc
limit 3;  -- limit a, b 跳过前a行，显示a+1到a+b行

-- 第二问
select 班级,
	sum(大于八十)as 人数, 
	avg(大于八十) as 占比
from(
	select t1.*, (case when t2.平均成绩>80 then 1 else 0 end) as 大于八十 
	from 学生表 t1
	left join
	(
		select 学号, avg(分数) as 平均成绩
		from 成绩表
		group by 学号
	) t2
	on t1.学号=t2.学号
) tmp
group by 班级;

关键点：有筛选条件的统计数量问题的万能模板

select sum(
case when <判断表达式> then 1
       else 0
end
) as 数量
from 信息表；

-- 备注如果是按照某一列的取值对另一列计算
case col1 when <判断表达式> then col2...
	else col2...
end

9.行列互换问题

长变宽

select 年,
	sum(case 月 when '1' then 值 else 0 end) as 'm1',
	sum(case 月 when '2' then 值 else 0 end) as 'm2',
	sum(case 月 when '3' then 值 else 0 end) as 'm3',
	sum(case 月 when '4' then 值 else 0 end) as 'm4'
from 长表
group by 年;

【题目】

学生的成绩表(表名：成绩表，列名：学号，课程，成绩)，转化为表结构列名：学号，语文成绩，数学成绩）

select 学号,
	sum(case 课程 when "语文" then 成绩 else 0) as 语文成绩,
	sum(case 课程 when "数学" then 成绩 else 0) as 数学成绩
from 成绩表
group by 学号;

10.连续出现N次的内容

【题目】

下面是学生的成绩表（表名score，列名：学号、成绩），使用SQL查找所有按照学号顺序排列，至少连续出现3次的成绩数值。

select distinct t1.score
from score t1, score t2, score t3
where t2.id - t2.id = 1 
	and t3.id - t2.id = 1
	and t1.score = t2.score
	and t2.score = t3.score

查询连续登录的用户

【复杂应用：连续登录三天以上的用户】除了隐式连接的方法，还可以利用窗口函数，更灵巧的解答。

表名：table_A （ 用户userid和登录时间time）

1）查询出最近7天的活跃用户，并对用户活跃日期进行排名（窗口函数dense_rank）

2）计算用户活跃日期及排名之间的差值（如果连续登录，差值相同）

3）对同用户及差值分组，统计差值个数

4）将差值相同个数大于等于3的数据取出，即为连续3天及以上活跃的用户

注意：去重（考虑到一天内多次登录）

datediff(d, day1, day2 ) = day2 -day1

date_sub(login_date, interval x day) = login_date - xdays

回答：

select count(distinct userid) 
from
(
	select userid, count(login_date) as logdays
	from
	(
		select distinct userid, login_date, -- 去重
			dense_rank() over (partition by userid order by time) as rk
		from table_a
		where datediff(d, login_date, getdate())<=7    -- 查询近七天里所有数据
		group by userid, login_date
	)  as tmp1
	group by userid,date_sub(login_date, interval tmp1.rk day)   -- 登录日期-排序
)  as tmp2
where logdays >= 3;

11. 留存率分析

11.1 指标定义：

  • 某日活跃用户数，某日活跃的去重用户数。

  • N日活跃用户数，某日活跃的用户数在之后的第N日活跃用户数。

  • N日活跃留存率，N日留存用户数/某日活跃用户数

输出结果按照每天来汇总

-- 每天的活跃用户数
select login_date,
	count(distinct user_id) as 活跃用户数
from user
where login_date between"2021-01-01" and "2021-02-01"
group by login_date;

-- 次日留存率
select u1.login_date,  -- 日期
	count(distinct u2.user_id), -- 次日留存
	count(distinct u2.user_id) / count(distinct u1.user_id)   -- 次日留存率
from user u1
left join user u2 
on u1.user_id = u2.user_id
and u1.login_date between "2021-01-01" and "2021-02-01" 
and datediff(u1.login_date, u2.login_date) = 1
group by u1.login_date;

11.2 输出次日、三日、七日留存数和留存率

同时输出次日、三日、七日留存数和留存率？——先算时间间隔，再利用casewhen来做不同的count

-- 只有一张登录表：
select u1.login_date,
	count (distinct case when by_days = 1 then u2.user_id else null end)) as 次日留存数
	count (distinct case when by_days = 1 then u2.user_id else null end)) / count(distinct u1.user_id) as 次日留存率
	count (distinct case when by_days = 3 then u2.user_id else null end)) as 三日留存数
	count (distinct case when by_days = 3 then u2.user_id else null end)) / count(distinct u1.user_id) as 三日留存率
	count (distinct case when by_days = 7 then u2.user_id else null end)) as 七日留存数
	count (distinct case when by_days = 7 then u2.user_id else null end)) / count(distinct u1.user_id) as 七日留存率
from
(
  select u1.user_id, u2.user_id, u1.login_date
    datediff(d, u1.login_date, u2.login_date) as by_days
  from user u1
  left join user u2
  on u1.user_id = u2.user_id
  and u1.login_date between "2021-01-01" and "2021-02-01" 
) as tmp
group by u1.login_date;
-- 有登录表（login_log）和 注册用户表(user_info)时把自连接换成两个表user_info left join login_log即可

11.3 真题

链家2018春招笔试面试：现有订单表和用户表，格式字段如下图：

订单表 时间 订单id 商品id 用户id 订单金额
用户表 用户id 姓名 性别 年龄 ****

1.查询2019年Q1季度，不同性别，不同年龄的成交用户数，成交量及成交金额

2.2019年Q1季度产生订单的用户数，以及在次月的留存用户数

-- 分组统计
select u.gender, u.age,
	count(distinct o.用户id) as 成交用户数,
	count(o.订单id) as 成交量,
	sum(o.订单金额) as 成交金额
from 订单表 o
inner join 用户表 u -- 用户数据可能有缺失，所以要innerjoin
on u.用户id = o.用户id
and o.时间 between "2019-01-01" and "2019-03-31"
group by u.gender, u.age;

-- 留存&次月留存
-- 错误回答：（错误原因：对每个用户购买的次月时间是不同的）
select count(distinct o1.用户id) ,
	count(distinct o2.用户id) / count(distinct o1.用户id) 
from 订单表 o1
left join 订单表 o2
on o1.时间 between "2019-01-01" and "2019-03-31"
and o2.时间 between "2019-04-01" and "2019-04-30";
-- 正确回答：
select count(distinct o1.userid) as Q1季度产生订单的用户
	count(distinct o2.userid) as 这些用户的次月留存率
from 订单表 o1
left join  订单表 o2
on o1.userid = o2.userid
and o1.时间 between "2019-01-01" and "2019-03-31"
where datediff(m, o1.时间, o2.时间) = 1;-- 时间间隔一个月

12.用户满意度分析（用or的多表联结）

【题目】

“满意度表”记录了教师和学生对课程的满意程度，包括教师编号、学生编号、和是否满意三列。

“ 用户表”记录了学校教师和学生的信息，包括三列：每个用户有唯一键 “编号”，“是否在系统”表示这个用户是否还在这所学校里，“角色”表示这个人是学生还是教师。

现在需要分析出学校里人员对课程的满意度。满意度的计算方式如下：

(对课程都满意且已存在当前教务系统中的用户) / (在学校里的人数)

select sum(case when 是否满意 is "满意" then 1 else 0) / count(是否满意)
from 满意度表
left join 用户表
on (满意度表.教师id =  用户表.id or 满意度表.学生id =  用户表.id)
where 是否在系统 is TRUE;

【题目】

Users 表里存放的是用户信息。一共三列：每个用户有唯一值（Users_Id） 。Banned 表示用户是否因为违规被禁止使用app。Role 记录了用户的角色，里面的值driver是司机，client是乘客，partner是合伙人。

Trips 表记录了各个出租车的行程信息。每段行程有唯一键（Id），还有Client_Id 、 Driver_Id，Status 列表示行程类型（ ‘completed’表行程正常结束, ‘cancelled_by_driver’ 表示行程因为司机原因取消，‘cancelled_by_client’表示行程因为乘客原因取消），还有request_at 订单时间列。

两个表的联结关系：Trips 表（Client_Id 、 Driver_Id） 和 Users 表中 Users_Id 的联结。 写一段 SQL 语句查出非禁止用户的取消率。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

select trips.request_at as 日期, 
 -- count(distinct (case when Status = "cancelled_by_driver" then trips.id else full end))/count(distinct trips.id) as 当日取消率
 sum(if(trips.status = 'completed',0,1)) / count(trips.status) -- 算订单数量，所以没有重复问题
from Trips 
left join Users 
on (trips.Client_Id  = Users.Users_Id or trips.Driver_Id  = Users.Users_Id)
and Users.Banned = "no"
group by trips.request_at;
# SQL
sql面试题系列（二） 上一篇
月读 | 2021.1期，我爱你 下一篇
Powered by Gridea | © 2019-2020 Theme By HsxyHao
学习笔记 & 日常生活