数据分析面试SQL题总结

作为失业青年，近2周经历了多家公司数据分析师面试。今年数据分析师岗位是真的卷，有几个明显的变化吧

1. boss直聘和猎聘上，岗位投递到约面试的转化率很低；
2. 面试中有SQL笔试的比例增加；
3. 同一个岗位的候选人多，整个面试流程时间较长。

SQL作为数据分析师最常用的工具，算是面试中必要的敲门砖吧。我也把近期经历的SQL笔试题做了总结分析，希望能帮助到大家。

一、内容总览

以下为笔试中常涉及的知识点，实际考察过程中知识点往往是相互组合进行考察

二、常见案例

示例用数据说明

1. 用户消费明细表 A，表中有字段用户id、消费时间、商品名称、商品类别、消费金额（user_id、date、product_name、product_class、trade_cnt）
2. 用户表B，表中有字段用户id、用户名称（user_id、user_name）
   
   

Q1：查询消费金额最多的用户

示例答案

-- 思路：通过sum函数求中各个用户的消费金额，然后按照消费金额倒序排列，用limit返回第一行
select
    user_id
  , sum(trade_cnt) as total_trade
from A
group by
    user_id
order by
    total_trade desc
limit 1;

知识点：
聚合函数 + 筛选排序（sum + order by + limit）
举一反三：
简单的最多最少问题直接用max、min等函数；求某个维度的最多最少，需要用聚合函数+筛选排序

Q2：查询单天消费金额超过100元的用户

示例答案：

-- 思路：通过sum函数求中各个用户、每天的消费金额，然后用having进行筛选
select
    user_id
  , date
  , sum(trade_cnt) as total_trade
from A
group by
    user_id
  , date
having sum(trade_cnt) > 100;

知识点：
使用having 对聚合后结果进行筛选

Q3：查询发生交易用户最多的一天

示例答案：

-- 思路：通过count distinct函数求每天交易的用户数，然后按照用户数倒序排列，用limit返回第一行
select
    date
  , count(distinct user_id) as total_user
from A
group by
    date
order by
    total_user desc
limit 1;

知识点：
使用count distinct 进行去重计数

Q4：给用户打标签。表B增加字段“是否重点用户”，如果交易金额大于100，则为重点用户标签

示例答案：

-- 思路：用户表为左表，关联用户维度的成交金额，然后按照用户成交金额判断是否为重点用户
-- 如果是多个条件判断用case when
select
    B.user_id
  , B.user_name
  , A1.total_trade
  , if(A1.total_trade >= 100, "重点用户", "非重点用户") as is_vip
from B
left join
-- 求每个用户的成交总金额
    (select
         user_id
       , sum(trade_cnt) as total_trade
    from A
    group by
        user_id) as A1
ON B.user_id = A1.user_id

知识点：
表连接 + 条件逻辑
IF函数的语法为：

IF(condition, value_if_true, value_if_false)
-- condition是一个布尔表达式，如果为真，则返回value_if_true，否则返回value_if_false。

CASE WHEN语句的语法为：

CASE
  WHEN condition1 THEN value1
  WHEN condition2 THEN value2
  ...
  ELSE value
END
-- 每个WHEN子句后面可以跟着一个条件表达式，如果满足该条件，则返回对应的值。

举一反三：
按照条件分类的问题，一般用if或case when语句，if函数适用于简单的条件判断，只能处理一个条件分支；而case when语句适用于复杂的条件判断，可以处理多个条件分支。

Q5：查询每个用户交易金额最多的商品

示例答案：

-- 思路1：将交易明细按照用户和商品聚合，然后按照用户分区、交易金额倒序排列并求出分区后的行数，筛选第一行
-- 思路2：然后按照用户分区，求出最大交易金额，筛选交易金额 = 用户最大交易金额的商品
select
    user_id
  , product_name
from (select
          user_id
        , product_name
        , total_trade
        , row_number() over (partition by user_id order by total_trade desc) as row_number
from (select
          user_id
        , product_name
        , sum(trade_cnt) as total_trade
from A
group by
    user_id
  , product_name) as A1) as A2
where row_number = 1

知识点：
考察的是开窗函数的基础用法

1. 基本语法

<窗口函数>(<表达式>) OVER ([PARTITION BY <列1>, <列2>, ...] [ORDER BY <排序列>])

2. 窗口函数类型：
   聚合函数：例如SUM、AVG、MIN、MAX等，用于对窗口中的数据进行聚合操作。
   排名函数：例如RANK、DENSE_RANK、ROW_NUMBER等，用于对窗口中的数据进行排序和排名操作。
   累积函数：例如CUME_DIST、PERCENT_RANK等，用于计算数据在窗口中的累积分布。

3. PARTITION BY子句：
   可选的，用于指定窗口函数的分区列。窗口函数将在每个分区内独立计算。
   可以指定一个或多个列作为分区条件。

4. ORDER BY子句：
   可选的，用于指定窗口函数的排序列。窗口函数将按照指定的排序列对数据进行排序。
   可以指定一个或多个列作为排序条件。

举一反三：
涉及到多个维度数据，求某个维度的子维度的最大、最小、平均数据时，都可以用开窗函数解决。
比如 “每个用户交易金额最多的商品类别”、“每个商品类别交易最多的商品”、“考试每个科目排名前N名的同学”等

Q6：连续登录问题

1. 查询连续7天有交易的用户；

-- 思路：按照用户id分区、交易时间进行排序，如果用户是连续7天有交易，那么前6行的日期 = 日期 - 6

/*明细表先要做个聚合处理*/
with A1 as(select distinct user_id, date from A)

select distinct
    user_id
from (select
          user_id
        , date
        , lag(date, 6) over (partition by user_id order by date)                               as lag6 -- 前6行的日期
        , date_sub(date, 6)                                                                    as datesub6 -- 当前行日期减6天的日期
        , if(date_sub(date, 6) = lag(date, 6) over (partition by user_id order by date), 1, 0) as is_7day_ac
from A1) as A2
where A2.is_7day_ac = 1

2. 求用户最大连续交易天数；

-- 思路：判断用户的行为日期是否连续，如果有多段连续，求连续的最大日期差。
-- 关键点：用户多段连续，需要将多段连续再进行分组编号

with A1 as (select distinct user_id, date from A)

select
    user_id
  , max(continuou_datediff) as max_continuou    -- 最大连续登录时长
from (select
          user_id
        , group_id
        , datadiff(max(date), min(date)) as continuou_datediff -- 连续登录时长
from (select
          user_id
        , date
        , datediff1
        , if(datediff1 > 1, 1, 0) as is_continuou -- 如果差为1，说明是连续的
          -- 关键代码，开窗函数中用rows between unbounded preceding and current row ,代表计算范围为分区的第一行到当前行
        , sum(if(datediff1 > 1, 1, 0)) over(partition by user_id order by date rows between unbounded preceding and current row) as group_id -- 用户连续登录时间段分组
from (select
          user_id
        , date
        , lag(date, 1) over (partition by user_id order by date)                 as lag1 -- 前1行的日期
        , datediff(date, lag(date, 1) over (partition by user_id order by date)) as datediff1 -- 当前行与前1行日期差
from A1) as A2) as A3
group by
    user_id
  , group_id
) as A4
group by
    user_id

3. 如果间隔1天，仍视为连续交易，求用户最大连续交易天数；
4. 如果间隔n天，仍视为连续交易，求用户最大连续交易天数；

-- 问题3和4均为2的衍生问题，核心修改的逻辑为如何按照条件给用户连续登录日期分组
-- 如果间隔1天仍视为连续交易，那么datefiff1 则必须要小于等于2（2023-07-15、2023-07-17）
sum(if(datediff1 > 2, 1, 0)) over(partition by user_id order by date rows between unbounded preceding and current row) 

-- 同样的间隔n天仍视为连续交易
sum(if(datediff1 > n+1, 1, 0)) over(partition by user_id order by date rows between unbounded preceding and current row) 

知识点：
开窗函数的高级用法，可以使用 rows between子句用于定义窗口函数计算的行范围

<窗口函数>(<表达式>) OVER ([PARTITION BY <列1>, <列2>, ...] [ORDER BY <排序列>] [<窗口范围> BETWEEN <起始行> AND <结束行>])

/*
<窗口范围>可以是以下几种形式之一：
	UNBOUNDED PRECEDING：表示从窗口的第一行开始，到当前行为止的所有行。
	<n> PRECEDING：表示从当前行向前数<n>行，包括当前行。
	CURRENT ROW：表示当前行。
	<n> FOLLOWING：表示从当前行向后数<n>行，包括当前行。
	UNBOUNDED FOLLOWING：表示从当前行开始，到窗口的最后一行为止的所有行。
	
<起始行>和<结束行>可以是以上任意一种窗口范围形式。
*/

叠buff

本文主要是总结近期面试中遇到的问题，也希望帮助到需要求的人，如果有问题欢迎交流。核心还是解释解题思路吧，SQL代码默认使用Hive Sql，大家在面试的时候可以先问问公司用的什么数据库。