5个必考的大厂SQL面试题转载

SQL几乎每个数据帖子都是一个必要的话题，下面分享几个常见的大工厂SQL习题。

（1找到连续数找到连续数找到连续数7日登录、连续天数登录、连续日登录、连续天数登录、连续30已登录天数的用户最大连续登录天数(小红书笔试、电信云访谈) --窗口函数

（2)要找出连续点击三次的用户数，以及没有其他人在其间点击的用户数 、最大连续天数变形问题(腾讯微保专访)--窗口函数

（3)计算平均薪资，不包括最高部门薪资和最低薪资(字节跳动面试)--窗口函数

（4保留额的计算和累计和的计算 --窗口函数，自链接(窗口函数，自链接(窗口函数，自链接(pdd面试）

（5）AB球队得分榜，取三连胜球员姓名 以及每次超越对手的选手的名字,（pdd面试）

---

这类问题要吃透了，再也不怕手撕了SQL还有笔试，其中最难的问题是(5）,整个采访整个采访整个采访sql它基本上是一个窗口函数的游戏，case when 它也被更频繁地测试。还有更多的考试。还有更多的测试。它也被更频繁地测试。

(1) 找出连续7日登录、连续天数登录、连续日登录、连续天数登录、连续30登录天数登录用户数

select *
from
（
  select user_id ,count(1) as num
  from
     (select user_id,date_sub(log_in_date, rank) dts
          f rom  (select user_id,log_in_date, 
                  row_number() over(partitioned by user_id order by log_in_date ) as rank
    from user_log
           )t
      )a
  group by dts
）b
where num = 7

(2) 找出连续点击三次的用户数，以及没有其他人在其间点击的用户数,

a该表记录有关点击流的信息，包括用户id 、点击时间、点击次数、点击时间

usr_id a a b a a a a

click_time t1 t2 t3 t4 t5 t6 t7

row_number() over(order by click_time) as rank_1 得到rank_1为 1 2 3 4 5 6 7

row_number() over(partition by usr_id order by click_time) 得到rank_2 为 1 2 1 3 4 5 6

rank_1- rank2 得到diff 为 0 0 2 1 1 1 1

此时，我们发现只需应用diff执行组计数大于执行组计数大于指挥组计数大于执行组计数大于3连续点击三次以上且其间没有其他点击的用户

select distinct usr_id
from    
(
   select *, rank_1- rank2  as diff
   from
  (
      select *,
      row_number() over(order by click_time) as  rank_1
      row_number() over(partition by usr_id order by click_time) as rank_2
      from a
   ) b
) c
group by diff,usr_id
having count(diff) >=3

(3) 计算不包括部门最高薪资和最低薪资的平均薪资(字节跳动面试)--窗口函数

emp 表

id 员工 id ，deptno 部门编号。部门编号salary 工资

利用窗函数降序和升序分别取出最高和最低的核心值。

select a.deptno，avg(a.salary)
from  
 (
 select *, rank() over( partition by deptno order by salary ) as rank_1
 , rank() over( partition by deptno order by salary desc) as rank_2 
 from emp
 )  a 
group by a.deptno
where a.rank_1 >1 and a.rank_2 >1

(4) 保留额的计算和累计和的计算 --窗口函数，自链接(窗口函数，自链接(窗口函数，自链接(pdd面试）

手机中的摄像头是最受欢迎的应用之一。以下是某手机厂商数据库用户行为信息表中的部分数据截图

现在手机制造商想要对手机进行分析 应用程序(摄像头)的活动。应用程序(摄像头)处于活动状态。 需要统计以下数据。需要统计以下数据。以下统计数据是必需的。需要以下统计数据。

待获取的数据格式如下。

select d.a_t,count(distinct case when d.时间间隔=1 then d.用户id     
               else null
               end) as  第二天保留第二天保留第二天保留, 
count(distinct case when 时间间隔=1 then d.用户id
               else null
               end) /count(distinct d.用户id) as 第二天保留率第二天保留率第二天保留率,
count(distinct case when d.时间间隔=3 then d.用户id     
               else null
               end) as  3日留存数 ,
count(distinct case when 时间间隔=3 then d.用户id
               else null
               end) /count(distinct d.用户id) as 3日留存率,
count(distinct case when d.时间间隔=7 then d.用户id     
               else null
               end) as  7日留存数 ,
count(distinct case when 时间间隔=7 then d.用户id
               else null
               end) /count(distinct d.用户id) as 7日留存率

from
(select *,timestampdiff(day,a_t,b_t) as 时间间隔
from (select a.用户id,a.登陆时间 as a_t ,b.登陆时间 as b_t
from 登录信息 as a  
left join 登录信息 as b
on a.用户id=b.用户id
where a.应用名称= 相机 AND b.应用名称=相机) as c) as d
group by d.a_t;

(5) AB球队得分榜，取三连胜球员姓名 以及每次超越对手的选手的名字（pdd）

我在评论中发现了一个类似的原始问题，这是我在采访中遇到的最难的问题

问题：两支篮球队打了一场势均力敌的篮球赛，比分交替。比赛结束后，你有两支球队得分的明细，记录下球队的情况team球员人数球员人数number球员姓名球员姓名球员姓名name, 得分分数score 得分时间、得分时间和得分时间scoretime(datetime)。现在球队想要对比赛中的出色表现进行表彰，所以请使用sql统计出

1)为球队连续得分三次(或以上)的球员名单

2)帮助各自球队从比赛中反弹的球员的名字和相应的时间。

首先创建相似表创建相似表首先创建相似表

CREATE TABLE basketball_game_score_detail(
   team  VARCHAR(40) NOT NULL ,
   number VARCHAR(100) NOT NULL,
   score_time datetime NOT NULL,
   score int NOT NULL,
   name varchar(100)  NOT NULL
);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:01:14,1,A1);
insert into  basketball_game_score_detail values(A,5,2020/8/28 9:02:28,1,A5);
insert into  basketball_game_score_detail values(B,4,2020/8/28 9:03:42,3,B4);
insert into  basketball_game_score_detail values(A,4,2020/8/28 9:04:55,3,A4);
insert into  basketball_game_score_detail values(B,1,2020/8/28 9:06:09,3,B1);
insert into  basketball_game_score_detail values(A,3,2020/8/28 9:07:23,3,A3);
insert into  basketball_game_score_detail values(A,4,2020/8/28 9:08:37,3,A4);
insert into  basketball_game_score_detail values(B,1,2020/8/28 9:09:51,2,B1);
insert into  basketball_game_score_detail values(B,2,2020/8/28 9:11:05,2,B2);
insert into  basketball_game_score_detail values(B,4,2020/8/28 9:12:18,1,B4);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:13:32,2,A1);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:14:46,1,A1);
insert into  basketball_game_score_detail values(A,4,2020/8/28 9:16:00,1,A4);
insert into  basketball_game_score_detail values(B,3,2020/8/28 9:17:14,3,B3);
insert into  basketball_game_score_detail values(B,2,2020/8/28 9:18:28,3,B2);
insert into  basketball_game_score_detail values(A,2,2020/8/28 9:19:42,3,A2);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:20:55,1,A1);
insert into  basketball_game_score_detail values(B,3,2020/8/28 9:22:09,2,B3);
insert into  basketball_game_score_detail values(B,3,2020/8/28 9:23:23,3,B3);
insert into  basketball_game_score_detail values(A,5,2020/8/28 9:24:37,2,A5);
insert into  basketball_game_score_detail values(B,1,2020/8/28 9:25:51,3,B1);
insert into  basketball_game_score_detail values(B,2,2020/8/28 9:27:05,1,B2);
insert into  basketball_game_score_detail values(A,3,2020/8/28 9:28:18,1,A3);
insert into  basketball_game_score_detail values(B,4,2020/8/28 9:29:32,1,B4);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:30:46,3,A1);
insert into  basketball_game_score_detail values(B,1,2020/8/28 9:32:00,1,B1);
insert into  basketball_game_score_detail values(A,4,2020/8/28 9:33:14,2,A4);
insert into  basketball_game_score_detail values(B,1,2020/8/28 9:34:28,1,B1);
insert into  basketball_game_score_detail values(B,5,2020/8/28 9:35:42,2,B5);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:36:55,1,A1);
insert into  basketball_game_score_detail values(B,1,2020/8/28 9:38:09,3,B1);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:39:23,3,A1);
insert into  basketball_game_score_detail values(B,2,2020/8/28 9:40:37,3,B2);
insert into  basketball_game_score_detail values(A,3,2020/8/28 9:41:51,3,A3);
insert into  basketball_game_score_detail values(A,1,2020/8/28 9:43:05,2,A1);
insert into  basketball_game_score_detail values(B,3,2020/8/28 9:44:18,3,B3);
insert into  basketball_game_score_detail values(A,5,2020/8/28 9:45:32,2,A5);
insert into  basketball_game_score_detail values(B,5,2020/8/28 9:46:46,3,B5);

这里我用这里我用lead和lag取每组的前几个值，它不完全等于最大联系天数，但可以用类似的想法求解，但使用lead和lag更容易理解当你做的时候更容易理解当你做的时候更容易理解

select distinct a.name ,a.team from
(
select *,lead(name,1) over(partition by team order by score_time) as ld1
,lead(name,2) over(partition by team order by score_time) as ld2
,lag(name,1) over(partition by team order by score_time) as lg1
,lag(name,2) over(partition by team order by score_time) as lg2
from table
) a
where (a.name =a.ld1 and a.name =a.ld2)
or (a.name =a.ld1 and a.name =a.lg1)
or (a.name=a.lg1 and a.name=a.lg2)

第二个分题没有在面试中完全做完，说下一个想法，现在我想的是当时的想法还是一个问题，而这个问题并不难，核心还是记录每一个时刻的累积分数表

SELECT TEAM,number,name,score_time,score,case when team=A then score else 0 end as A_score
,case when team=B then score else 0 end B_score
FROM basketball_game_score_detail
ORDER BY SCORE_time

每一时刻的累计得分表如下

select team,number,name,score_time,A_score,b_score
,sum(A_score)over(order by score_time) as  a_sum_score2
,sum(b_score)over(order by score_time) as b_sum_score2
from 
(
  SELECT TEAM,number,name,score_time,score,case when team=A then score else 0 end as A_score
  ,case when team=B then score else 0 end B_score
  FROM basketball_game_score_detail
  ORDER BY SCORE_time
) as x

计算每个时刻的累计分数差和前一次的累计分数差，只要两者的对立面的符号是超车时刻。感觉这个想法还是比较简单。

select *,score_gap*last_score_gap
from 
(
 select  *,a_sum_score2-b_sum_score2 as score_gap 
 ,lag(a_sum_score2-b_sum_score2,1)over(order by score_time) as last_score_gap
 from 
 (
  select team,number,name,score_time,A_score,b_score
  ,sum(A_score)over(order by score_time) as  a_sum_score2
  ,sum(b_score)over(order by score_time) as b_sum_score2
  from (
   SELECT TEAM,number,name,score_time,score,case when team=A then score else 0 end as A_score
   ,case when team=B then score else 0 end B_score
   FROM basketball_game_score_detail
   ORDER BY SCORE_time
  ) as x
 ) as y
) as z
where z.score_gap*last_score_gap<=0
and a_sum_score2<>b_sum_score2

当然，不仅会有当然，会有不止只是SQL课程开课了，我们还会有工厂大数据大师帮你答疑解惑，包括技术、求职、面试、抓紧时间！

文章来源：华丽小海螺文章来源：华丽小海螺

---

这是我开发的一个小型机器人公共，目前添加了天气查询955公司名单，注意时间查询；以后还会增加图片功能和每日送书抽奖活动，以及猥亵功能，欢迎体验、拥抱。

一个机器人公共号码在线，欢迎来到猥亵

推荐阅读:
入门: 最完整的零基学习最全面的零基学习最完整的零基学习Python的问题  | 从零开始学习从零基础学习从零基础学习8个月的Python  | 实战项目 |学Python这是捷径这是捷径就是这条捷径
干货:爬行豆瓣短评，电影《后来的我们》 | 38年NBA最佳球员分析最佳球员分析 |   从万众期待到口碑惨败！唐探3令人失望  | 笑新一天图龙记笑新一天图龙集 | 谜语之王回答灯谜之王灯谜之王谜语之王 ｜用Python人山人海素描图人山人海素描图人山人海 Dishonor太火了，我用机器学习做了一个迷你推荐系统电影
趣味:弹球游戏  | 九宫格  | 漂亮的花 | 两百行Python日常酷跑游戏日常酷跑游戏日常酷跑游戏!
AI: 会写诗的机器人会写诗的机器人会写诗的机器人 | 给图片上色给图片上色给图片上色 | 预测收入 | 《耻辱》太火了，我用机器学习做了一部迷你推荐系统电影
小工具: Pdf转Word易于修复表单和水印！易于处理的表单和水印！易于修复表单和水印！简单的表格和水印！ | 一键把html将页面另存为网页另存为网页另存为pdf！|  再见PDF提款费！提款费！提款费！提款费用！ | 用90构建最强大的代码行构建最强大的代码行构建最强大的代码行PDF转换器，word、PPT、excel、markdown、html一键转换 | 制作一个固定的低成本机票提醒！制作一张别针的低价机票提醒！ ｜60代码行做了一个语音墙纸切换，天天见女士！

年度弹出式文案年度弹出式文案

• 1). 卧槽！Pdf转Word用Python轻松搞定 ！

• 2).学Python闻起来好香！我用100一行代码做了一个网站，帮助人们做了一行代码，做了一个网站，帮助了人们做了一行代码，帮助了人们PS旅行图片赚鸡腿吃旅行图片赚鸡腿

• 3).第一次播放量过亿，火爆全网，我分析了《波妹》，发现了这些秘密

• 4). 80一行行代码！使用Python让救济金做正确的事做做的人做好事的人A梦分身

• 5).你必须掌握的东西你必须掌握20个python代码，简短而紧凑，永无止境的有用代码，简短而甜蜜，永无止境的有用的代码，简短而紧凑，永无止境的使用代码，简短而甜蜜，永无止境的用途

• 6). 30个Python古怪技能集古怪小贴士收藏古怪技能集

• 7). 我总结的80《菜鸟学习专页》《菜鸟学习专页》《菜鸟学习》Python精选干货.pdf》,都是干货

• 8). 再见Python！我要学Go了！2500词深度分析词深度分析词深度分析 ！

• 9).发现一只舔狗的福利！这Python爬虫神器太酷了，不能自动下载女孩的照片

点击阅读原文点击查看点击点击阅读点击阅读原文点击查看B放我鸽子看录像！站在我的录像带上！在视频里放我鸽子！站在我的录像带上！