微信号:bit_tiger

介绍:“有趣,有用,有效”.刷项目,做实战,捅破技术的那层纸.

能做对这几道SQL题,你的面试可能就稳了

2018-01-13 10:07 BitTiger

SQL在Business Analyst, Data Analyst, 甚至Data Scientist面试中都占据了非常重要的部分。大部分人都会觉得SQL很简单,但一到面试就挂。在这里我们列举几个SQL题目,熟练掌握这些题,你的面试基本不会被难倒。

这里有两张表:

Table a: HR Hires Table (记录员工入职时的信息)

Table b: Promotions Table (记录员工的Promotion和新的Title)

热身问题:小王,你来说一下哪些员工从来没有升过职?

要求是:在以下两种不同条件下,分别解决这个问题。

条件一:不能Join table

条件二:必须Join table

大家先自己想一下。

防偷看

防偷看

防偷看

防偷看

防偷看

防偷看

以下为答案:

1)不能Join table

Select * from a where a.HR_ID not in (select distinct HR_ID from b)

2)必须Join table

Select

a.*

from a

left join b

on a.HR_ID = b.HR_ID

Where b.HR_ID is null  

这道题考察了Left join的使用。

我们进一步思考,如果最后一行的Where b.HR_ID is null 中,“Is null”改成 “is not null”,是不是就能得到有过Promotion的员工呢?

答案是肯定的,但并不全对,因为会得到所有重复的员工信息,所以写这段代码时不要忘了Distinct。

Select

distinct

a.*

from a

left join b

on a.HR_ID = b.HR_ID

Where b.HR_ID is null  

在做SQL题时,一定要注意Distinct,Distinct可以帮助去重,是一个需要注意的重要细节,是一个习惯。宁可多写也不要被面试官抓住把柄。

我们再看下一个问题:

Table a

Table b

这里我们给Table b加了一列作为Primary key。

小王,你来说一下:哪位员工升职次数最多?

Select b.HR_ID, count(distinct Promotion_ID)

From b

Group by 1

Order by 2 desc

Limit 1

还可以这样写:

Table c:

Select b.HR_ID, count(distinct Promotion_ID) as promo_cnt

From b

Group by 1

Order by 2 desc

建一个Table c,每一行表示这个人升职了多少次。通过Table c找出谁升职次数最多:

Select distinct c.HR_ID

From c

Where c.promo_cnt = (select max(promo_cnt) from c)

那么,哪位员工升职次数第二多?

Select distinct c.HR_ID, c.promo_cnt

From c

Where c.promo_cnt <> (select max(promo_cnt) from c)

Order by 2 desc

Limit 1

哪位员工升职次数第五多?

在这里推荐大家使用Rank函数,写出来简单易懂。你会用Rank,很多难题会迎刃而解。如果你在解决“第二多”的问题时就直接用Rank,面试官可能就会觉得你的SQL技能在一个比较好的水平。

Rank() over(partition by XXX order by XXX)

Rank函数是给元素排序,不需要用Group by,就能给每个元素一个index。需要注意的是,如果用Rank,Query中要用Qualify而不是Where,

Select distinct c.HR_ID, c.promo_cnt,

Rank() over(order by c.promo_cnt desc) as rnk

From c

Qualify rnk = 5

现在,如果不允许你用Raw number,Limit,以及Rank,怎么找出升职次数第五多的员工呢?

这考察的是Self join,代码如下:

Select c1.HR_ID, Count(distinct c2.promo_cnt) as rnk

From c c1 left join c c2

On c1.promo_cnt <= c2.promo_cnt

Group by 1

Having rnk = 5

用这种方法,找第n个人也是可以实现的。

最后再看一个拓展问题:

如果Table里有重复值,你如何解决重复值?

比如现在有100个人,升职次数有两个人并列第一,他们都升职了10次。而我只想要Top 1的这一个值,这时Self join可能没法解决这个问题,但用Rank可以解决。

Select distinct c.HR_ID, c.promo_cnt,

Rank() over(order by c.promo_cnt, c.hr_id desc) as rnk

From c

Qualify rnk = 5

这样每次只会取出一条记录。

最后给大家留一个思考题:

哪一种Promotion最耗时间?(比如从Analyst升到Sr. Analyst,为一种Promotion)

以上总结起来有两个要点:

  • 熟练掌握Rank以应对测试

  • 如果不用Rank,知道使用Cross join来完成排序

3个月三大企业级BA项目

  • 项目一:预测Amazon销售增长

  • 项目二:实现Google产品迭代

  • 项目三:信用欺诈——金融用户数据分析

课程亮点

  • 三个月完成互联网公司,电商,金融机构三大企业级实战项目

  • 两位业内重量级讲师亲自指导,手把手解决核心项目问题

  • 32小时SQL, R, Python, Notebook核心技术与高频面试题知识考点精讲

  • 12小时面试大冲刺求职辅导,职场规划个性化规划

  • 10小时经典案例总结,紧密结合面试必考题型与案例

  • 40小时BitTiger独家SQL, NoSQL, R, Python, Tableau基础知识视频

报名第一节免费公开课

复制链接或点击“阅读原文”

http://t.cn/RQ4SZnS

30分钟与课程组老师一对一咨询,帮助你分析问题及寻找解决方案。

课程组老师会在提交申请后24小时内进行简历背景评估,并电话回访提供Career指导。




 
BitTiger 更多文章 “你的才华,撑得起自己的野心吗?”英特尔无人车PM分享跌宕起伏职场路 2017年面试真题:如何定位单向链表中循环的起始节点 直播丨不做“伪工作者”!程序员职场升职的“奥秘” 只剩下12小时,跟我逃离刷题苦海 沁原直播:2018年春招,你会这100道算法题就够了
猜您喜欢 深圳机场与华为签署战略合作框架协议——跨界合作打造“最具体验”的“智慧机场” rm -rf 惨剧的补救过程:重温 17 篇 Linux 热文 手快活更好!不到最后,怎知分晓!这些“不可告人”的秘密,你发现了吗?! 中移动DCOS测试验证启动会在杭召开 iOS开发中常用到的加密方式