【数据分析】- SQL必做常见面试题

1、找出每个用户得分最高的视频，得分相同时按照视频id选择最大的（原：找出每个用户哪一类型得分最高的视频，视频相同时按照视频id选择最大的）（快手）

参考答案

表a：uid：用户id；aid：视频id；score：得分

2、找出开播三分钟内无人进入的直播房间号（快手）

参考答案

主播表a：upid：主播id；rid：直播间id；stime：开播时间

观众表b：uid：观众id；rid：进入的直播间id；intime：进入时间

3、sql window function/ full join（字节跳动）

参考答案

1） window function

SQL中常用的窗口函数基本语法如下，常用的窗口函数有排序和聚合两类函数：

假设现有学生的分数表scores：

A. 排序函数

rank、dense_rank、row_number都是排序函数，但是排序的机制不一样，当排序中存在相同的值时会出现不同的排序结果，根据以下查询方式：

得到的结果如下：

B. 聚合函数

聚合函数即为max、min、sum这类常用的函数，只是计算方式略有不同，我们尝试使用以下代码进行SQL查询：

得到的结果如下：

2） full join

全联接full join表示在将左表和右表进行连接时，只要在其中一个表存在就会返回该行，覆盖了left join和right join的结果。

4、找出个分类下粉丝数提升（跟昨天）最多的20个id（字节跳动）

表a：uid：用户id；logdate：记录日期；fans：粉丝数

5、SQL窗口函数、如何去重（京东）

参考答案

假设table表中有字段a、b、c，现需要对字段a、b进行去重，在SQL中通常有三种方法能够实现去重的功能：

1） DISTINCT 关键字

使用DISTINCT去重的方法很简单，在查询数据时在字段前增加DISTINCT关键字既可对字段内容进行去重。如下代码将输出table表中字段a和b的组合不重复的数据：

2） GROUP BY关键字

使用GROUP BY进行去重的方法和DISTINCT类似，仅需在查询语句末端增加GROUP BY即可，而且能够对分组数据进行筛选。

3） 窗口函数

使用窗口函数进行去重时，比DISTINCT和GROUP BY稍微复杂些，可以采用窗口函数+over(partition by 去重字段)的方式。去重方式如下：

6、给定用户表，求用户的次日留存率（京东）

参考答案

假设usrlogs用户表记录了用户的登陆日期，如下：

首先计算用户登陆的日期间隔，并写入临时表date_interval：

然后根据用户登陆的日期间隔筛选出留存的用户，从而计算出次日留存及次日留存率：

次日留存考虑的是某日登陆的用户次日依旧登陆的用户量及比率，而三日和七日分别考虑的是某日登陆的用户三日和七日后登陆的用户量及比率，因此只需将上述代码中day_diff日期间隔设置为3、7即可计算出三日和七日留存及留存率。

7、SQL行列转换/SQL代码题：行、列转换（小米、腾讯）

参考答案

假设数据库只现有一张销售记录表Sales：

对该数据表进行行列转换的代码如下：