数据分析大厂面试：常考SQL15题

虾米数据

已于 2024-12-08 21:17:11 修改

阅读量2.1k

点赞数 22

分类专栏：数据分析面试文章标签：数据分析 sql

于 2024-10-28 18:29:24 首次发布

本文链接：https://blog.youkuaiyun.com/codeape365/article/details/143309607

版权

数据分析面试专栏收录该内容

1 篇文章

订阅专栏

大纲

番外、数据分析师大礼包
1、App 使⽤频率分析
2、⽹约车司机收益统计
3、⽤户最⼤连续登陆天数
4、计算除去部⻔平均⼯资（除去最⾼最低值）
5、【滴滴2023真题】
6、 "双⼗⼀ "活动的电商GMV分析
7、⽹站访问量分析
8、⽤户购物信息统计
9、连续售出的商品
10、优惠券使⽤分析
11、商品销量同环⽐

在成为数据分析师的成长的路上，小编为大家准备几个大礼包，分别为经典的数据分析书籍电子版，经典机器学习/数据分析面试题
在这里插入图片描述

资料获取方式可以查看文章底部

番外、数据分析师大礼包

1、App 使⽤频率分析

【题⽬1】现有⼀张⽤户使⽤ App 时间表 middle_app_login ，middle_app_login 表的数据如下表所⽰：据该表统计出每个⽤户每次退出App与下⼀次登录App间隔的平均时间，如果⽤户只登录过⼀次App，则不统计，要求输出的平均时间的单位为分钟，并将其四舍五⼊保留⼀位⼩数。输出内容包括：user_id（⽤户ID）、avg_minute（平均间隔时间）；

1 -- mysql
2 SELECT *
3 FROM middle_app_login;
4 -- user_id（⽤户ID）：VARCHAR start_time（登录App时间）：DATETIME end_time（退出App 时间）：DATETIME
5 +---------+---------------------+---------------------+
6 | user_id | start_time           | end_time             |
7 +---------+---------------------+---------------------+
8 | u001    | 2021-04-01 10:12:30 | 2021-04-01 11:13:21 |
9 | u002    | 2021-04-02 08:40:21 | 2021-04-02 10:13:41 |
10 | u003    | 2021-04-02 15:31:01 | 2021-04-02 15:54:42 |
11 | u001    | 2021-04-04 13:25:40 | 2021-04-04 17:52:46 |
12 | u003    | 2021-04-06 07:10:20 | 2021-04-06 08:03:15 |
13 | u001    | 2021-04-09 18:20:34 | 2021-04-09 18:23:58 |
14 | u001    | 2021-04-10 14:25:55 | 2021-04-10 15:01:25 |
15 +---------+---------------------+---------------------+

【题⽬1解析】本题使⽤LEAD()函数对每个⽤户登录App的时间进⾏分组排序，⽣成新的⼀列，以构造出上⼀次退出App的时间与下⼀次登录App的时间在同⼀⾏的表格结构，⽅便之后进⾏处理。然后筛选出为⾮空的⾏，使⽤TIMESTAMPDIFF()函数计算start_time_lead与end_time的分钟差，再求平均值，并将其四舍五⼊保留⼀位⼩数即可得到结果。涉及知识点：⼦查询、⽇期/时间处理函数、窗⼝函数、空值处理、⼩数保留、分组聚合。参考代码如下：

1 -- mysql --按照解析的写法
2 SELECT  user_id,
3    ROUND(AVG(TIMESTAMPDIFF(MINUTE, end_time, start_time_lead)), 1) AS avg_minute
4 FROM 5 (
6     SELECT  user_id,
7             start_time,
8             end_time,
9            LEAD(start_time, 1) OVER
10                     (PARTITION BY user_id ORDER BY start_time) AS
start_time_lead
11     FROM middle_app_login
12 ) a
13 WHERE start_time_lead IS NOT NULL
14 GROUP BY user_id;
15 +---------+------------+
16 | user_id | avg_minute |
17 +---------+------------+
18 | u001    |    4293.3 |
19 | u003    |    5235.0 |
20 +---------+------------+

2、⽹约车司机收益统计

【题⽬2】现有—张⽹约车订单表 middle_car_order ，该表记录了某天的⽹约车订单相关信息，司机的收⼊为订单⾦额的80%（表中订单⾦额的单位为元），如果司机当天的订单数量>=5且总订单⾦额>=100 ，则能收到额外补贴10元。请统计当天各司机的收⼊，并将结果按照收⼊降序排列且四舍五⼊保留两位⼩数。输出内容包括：driver_id（司机ID）、total_order（总订单量）、total_income（总收⼊），结果样例如下图所⽰： middle_car_order 数据如下表所⽰：

1 --mysql
2 SELECT * FROM middle_car_order;
3 -- order_id（订单ID）：VARCHAR driver_id（司机ID）：VARCHAR 4 -- order_amount（订单⾦额）：DOUBLE
5 +----------+-----------+--------------+
6 | order_id | driver_id | order_amount |
7 +----------+-----------+--------------+
8 | o001      | d001       |          15.6 |
9 | o002      | d002       |          36.5 |
10 | o003      | d001       |          30.1 |
11 | o004      | d002       |          10.6 |
12 | o005      | d001       |          26.2 |
13 | o006      | d001       |          14.6 |
14 | o007      | d003       |          28.9 |
15 | o008      | d001       |           8.8 |
16 | oΘΘ9      | dΘΘ2       |          13.3 |
17 | oΘ1Θ      | dΘΘ1       |          29.4 |
18 | +----------+-----------+--------------+

【题⽬2解析】参考代码如下：

1 --mysql
2 SELECT  a.driver_id,
3          a.total_order,
4     CASE WHEN total_order>=5 AND total_amount>=1ΘΘ THEN ROUND(total_amount大Θ .8+1Θ
5     ELSE ROUND(total_amount 大 Θ .8, 2)
6     END AS Itotal_income I
7 FROM 8 (
9      SELECT  driver_id,
1Θ              COUNT(driver_id) AS Itotal_order I,
11               SUM(order_amount) AS Itotal_amount I
12      FROM middle_car_order
13      GROUP BY driver_id
14 ) a
15 ORDER BY total_income DESC;

3、⽤户最⼤连续登陆天数

【题⽬3】为了看有哪些忠实⽤户，积极使⽤产品，需要计算近30天内⽤户的连续登陆天数分布；现有⼀张⽤户登陆 App 时间表 user_id ，login_time 表的数据如下表所⽰：

1 --mysql
2 --得到 t_1表
3 select  distinct user_id,
4          date(log_time) as log_date
5 from user_login
6 where timestampdiff(day, login_time, now()) <= 30; 7
8 --使⽤row_number() over(partition by order by )进⾏排序 9 --得到 t_2表
10 select  user_id,
11        log_date,
12         row_number() over(partition by user_id order by log_date) as rank
13 from 14 (
15     select  distinct user_id,
16            date(log_time) as log_date
17     from user_login
18     where timestampdiff(day, login_time, now()) <= 30
19 )as t_2
20
21 --show t_2
22 select * from t_2;
23 +----------+-----------+--------------+
24 | user_id | login_date | rank |
25 +----------+-----------+--------------+
26 | u_id1     | 2021-08-08 |  1  |
27 | u_id1     | 2021-08-09 | 2  |
28 | u_id1     | 2021-08-10 | 3  |
29 | u_id1     | 2021-08-11 | 4  |
30 | u_id2     | 2021-08-07 |  1  |
31 | u_id2     | 2021-08-20 | 2  |
32 | u_id2     | 2021-08-21 | 3  |
33 +----------+-----------+--------------+

【题⽬3分析】通过观察可以发现，若同组⽤户login_date是连续的，分别减去rank后所得到的结果是⼀样的，2021-08-08 - 1 = 2021-08-07 ，2021-08-09 - 2 = 2021-08-07 等等。使⽤date_sub函数获得新的列gap和新表t_3

1 --得到 t_3
2
3 select *
4 from 5 (
6     select  user_id,
7            login_date,
8            date_sub(login_date, interval rank day) as gap
9     from t_2
10 )as t_3 
--show t_3
select * from t_3;
+------------+--------------------+--------------+
| user_id | login_date | rank | gap |
+------------+--------------------+--------------+
| u_id1 | 2021-08-08 | 1 | 2021-08-07 |
| u_id1 | 2021-08-09 | 2 | 2021-08-07 |
| u_id1 | 2021-08-10 | 3 | 2021-08-07 |
| u_id1 | 2021-08-11 | 4 | 2021-08-07 |
| u_id2 | 2021-08-07 | 1 | 2021-08-06 |
| u_id2 | 2021-08-20 | 2 | 2021-08-18 |
| u_id2 | 2021-08-21 | 3 | 2021-08-18 |
+------------+--------------------+--------------+

最后按照 gap 分组统计不同的user_id ，得到⽤户连续登陆的天数分布整体sql汇总如下：

1 --sql汇总 得到连续登陆⽤户分布 2
3 select  num,
4        count(distinct user_id) as user_cnt
5 from
6 (   -- 按user_id, date_sub结果gap分组，计数求得连续登陆天数 num
7     select  user_id,
8            gap,
9            count(login_time) as num
10     from
11     (   -- 主要求出data_sub的结果gap，结果相同则代表连续登陆
12        select  user_id,
13                 log_date,
14                date_sub(login_time, interval rank day) as gap
15         from
16         (   -- 主要求出rank 辅助字段
17            select  user_id,
18                     log_date,
19            row_number() over(partition by user_id order by log_date) as rank
20            from 21            (
22                -- 筛选出30天内的数据，  row_number 进⾏分组排序，得出t_1表
23                select  distinct user_id,
24                        date(log_time) as log_date
25                from user_login
26                 where timestampdiff(day, login_time, now()) <= 30
27            )as t_2

4、计算除去部⻔平均⼯资（除去最⾼最低值）

【题⽬4】emp 表字段 id员⼯ id ，deptno部⻔编号，alary ⼯资

【题⽬4分析】核⼼是使⽤窗⼝函数降序和升序分别排⼀遍就取出了最⾼和最低，再⽤avg函数算平均


1 -- sql
2 select  t_1.deptno，
3        avg(t_1.salary) as avg_salary
4 from 5 (
6     select  *,
7           rank() over( partition by deptno order by salary ) as rank_1,
8           rank() over( partition by deptno order by salary desc) as rank_2
9     from emp
10 ) as t_1
11 where t_1.rank_1 >1 and t_1.rank_2 >1
12 group by a.deptno

5、【滴滴2023真题】

【题⽬5】

“订单信息表”⾥记录了巴西乘客使⽤打车软件的信息，包括订单呼叫、应答、取消、完单时间表名：订单信息表

order_id	passenger_id	call_time	grab_time	cancel_time	finish_time
70361	1	2018/3/9 9:07	2018/3/9 9:09	1970/1/1 1:01	2018/3/9 9:37
70362	1	2018/3/9 22:07	2018/3/9 22:07	2018/3/9 22:08	1970/1/1 1:01
70363	2	2018/3/9 20:10	1970/1/1 1:01	2018/3/9 20:11	1970/1/1 1:01
70364	3	2018/3/10 8:10	1970/1/1 1:01	1970/1/1 1:01	1970/1/1 1:01
70365	4	2018/3/12 9:00	2018/3/12 9:01	1970/1/1 1:01	2018/3/12 9:30

字段释义：

字段	中⽂名称	解释
order_id	订单ID	呼叫订单识别号
passenger_id	乘客ID	乘客识别号
call_time	呼叫时间	乘客从应⽤上发出需要⽤车的请求的时间点（北京时间）
grab_time	应答时间	司机点击接单的时间点（北京时间）
cancel_time	取消时间	司机或乘客取消订单的时间点（北京时间）
finish_time	完单时间	司机点击到达⽬的地的时间带你（北京时间）

指标释义：

指标名称	含义	统计⼝径
应答率	呼叫订单被应答的⽐例	应答订单/呼叫订单
完单率	呼叫订单被完成订单	完成订单/呼叫订单
呼叫应答时间	被应答订单从呼叫到被应答平均时⻓	从呼叫到被应答时⻓总和/被应答订单数量

注意：（ 1）表中的时间是北京时间，巴西⽐中国慢11⼩时；（2）应答时间列的数据值如果是“ 1970”年，表⽰该字段为空，⽐如应答时间为空就是该订单⽆应答

1、订单的应答率，完单率分别是多少

应答率=应答订单数/呼叫订单数

完单率=完成订单数/呼叫订单数

1 select  sum(if(year(grab_time)<>1970,1,0))/count(call_time) as '应答率 ',
2         sum(if(year(finish_time)<>1970,1,0))/count(call_time)as '完单率 '
3 from didi

2、呼叫应答时间有多⻓

1 -- 使⽤timestampdiff函数，计算应答时间与呼叫时间之间的时⻓
2 select sum(TIMESTAMPDIFF(MINUTE,call_time,grab_time))/count(grab_time) as '呼叫应
3 from didi
4 where year(grab_time)<>1970;

3、从这⼀周的数据来看，呼叫量最⾼和最低的是哪⼀个⼩时

由于在⽇期格式化中，我们会涉及到需要修改表中的⽇期数据，因此考虑⽤update语句。⽽修改表的具体操作会涉及到⽇期数据类型之间的转换，我们考虑⽤cast函数；由于表中的时间应是datetime的格式，也就是精确到时分秒（YYYY-MM-DD HH:mm:ss）

update didi set call_time=cast(call_time as datetime); 
update didi set grab_time=cast(grab_time as datetime); 
update didi set cancel_time=cast(cancel_time as datetime); 
update didi set finish_time=cast(finish_time as datetime);

由于数据中的时间为北京时间，⽽且已知巴西⽐中国慢11⼩时，因此我们这⾥使⽤ date_sub函数

update didi set call_time= date_sub(call_time, interval 11 hour) ; 
update didi set grab_time= date_sub(grab_time, interval 11 hour) ; 
update didi set cancel_time= date_sub(cancel_time, interval 11 hour) ;
update didi set finish_time= date_sub(finish_time, interval 11 hour) ;

由于题⽬中要求的是“ 哪⼀个⼩时” ，因此我们⾸先将数据格式化转换成⼩时。新增⼀列来表⽰时间中的“ ⼩时”,列名设为call_time_hour

1 -- 添加列
2 alter table didi add column call_time_hour varchar(255);
3 /**
4 给列添加数据
5 %k表⽰显⽰的是24⼩时制中的⼩时 6 */
7 update didi
8 set call_time_hour = date_format(call_time,'%k'); 9
10 -- 按照 ⼩时分组统计
11 select  *,
12          (case when rank1 = 1 then '最⾼ '
13             when rank2 = 1 then '最低 '
14             else null
15             end) as maxmin_type
16 from 17 (
18     select  *,
19            row_number() over(order by order_cnt desc) as rank1,
20            row_number() over(order by order_cnt) as rank2
21     from 22     (
23        select  call_time_hour,
24                count(order_id) as order_cnt
25         from didi
26        group by call_time_hour
27     ) as t_1
28 )as t_2
29 where rank1 = 1 or rank2 = 1

4、⽤户连续两天呼叫订单的⽐例有多少


1 --sql
2 select  scd_psg_cnt/ttl_psg_cnt as psg_rate  --连续两天呼叫订单的⽐例
3 from
4 (
5     select count(distinct b.passenger_id) as scd_psg_cnt,
6                                --这些⽤户是有连续第⼆天有呼叫订单的
7            count(distinct a.passenger_id) as ttl_psg_cnt
8                                --这些⽤户是全部有订单⽤户
9     from didi a left join didi b on a.passenger_id = b.passenger_id
10     where datediff(a.call_time,b.call_time) = 1
11 ) as t_1

6、 "双⼗⼀ "活动的电商GMV分析

拓展:商品交易总额（Gross Merchandise Volume，简称GMV）是成交总额（⼀定时间段内）的意思。多⽤于电商⾏业，⼀般包含拍下未⽀付订单⾦额

现有⼀张电商的 GMV 数据表 easy_gmv_info ，该表记录了某商家在“ 双⼗⼀ ”活动前后的GMV数据信息，easy_gmv_info 表的数据如下表所⽰：

1 --mysql
2 SELECT * FROM easy_gmv_info;
3 -- date(⽇期): DATE mall_gmv(GMV): INT
4 +------------+----------+
5 | date       | mall_gmv |
6 +------------+----------+
7 | 2020-11-04 |   12325 |
8 | 2020-11-05 |   15497 |
9 | 2020-11-06 |   13216 |
10 | 2020-11-07 |   16548 |
11 | 2020-11-08 |   17367 |
12 | 2020-11-09 |   20124 |
13 | 2020-11-10 |   37325 |
14 | 2020-11-11 |  134367 |
15 | 2020-11-12 |   54331 |
16 | 2020-11-13 |   22212 |
17 | 2020-11-14 |   16312 |
18 | 2020-11-15 |   14384 |
19 | 2020-11-16 |   12314 |
20 | 2020-11-17 |   13146 |
21 +------------+----------+

【题⽬6】查询2020年11⽉11⽇起的⼀周时间内，相⽐7天前GMV的变化率。输出内容包括：date（⽇期）、ratio（GMV变化率），结果样例如下图所⽰：

【题⽬6解析】使⽤LAG()函数构造偏移7天的数据，并通过做差得到差异gmv_diff，进⽽得到差异率 gmv_rate ，然后使⽤CONCAT()函数拼接%号，并且将结果限定在2020年11⽉11⽇到2020年11⽉17 ⽇，从⽽得到最终结果。涉及知识点：⼦查询、窗⼝函数、分组聚合。参考代码如下：

1 SELECT  date,
2        CONCAT(gmv_rate, '%') AS ratio
3 FROM 4 (
5      SELECT  date,
6              mall_gmv,
7           --(mall_gmv - LAG(mall_gmv, 7) OVER (ORDER BY `date`)) AS gmv_diff
8               (mall_gmv - LAG(mall_gmv, 7) OVER (ORDER BY date))
9                / LAG(mall_gmv, 7) OVER (ORDER BY date) * 100  AS gmv_rate
10      FROM easy_gmv_info 11 ) t
12 WHERE date BETWEEN '2020-11-11' AND '2020-11-17'; 13

7、⽹站访问量分析

现有⼀张⽹站访问量情况表 easy_website_visit ，该表记录了⽹站每⽇的访问量， easy_website_visit 表的数据如下表所⽰：

mysql> SELECT * FROM easy_website_visit;
-- data_content  (日期访问量)  :  VAR CHAR  该字段的前8位为日期,后4位为访问量。
-- 例如: 201812011241  表示2018年12月01日的网站访问量为1241
+--------------+
| data_content |
+--------------+
| 201812011241 |
| 201812022493 |
| 201812030845 |
| 201812041230 |
| 201912012317 |
| 201912022520 |
| 201912031945 |
| 201912042031 |
| 202012013015 |
| 202012022914 |
| 202012032319 |
| 202012043143 |
+--------------+

【题⽬7】查询每年最⼤⽹站访问量（不需要输出对应的⽇期）。输出内容包括：visit_year（访问年份）、max_visit（最⼤访问量）

【题⽬7解析】通过SUBSTR()函数将数据中的年份和访问量截取出来，并分别命名为 visit_year和 max_visit ，然后使⽤RANK()函数根据年份进⾏分组降序排名，在⼦查询外部将排名为1的结果取出，即为每年的最⼤访问量数据。涉及知识点：字符串处理函数、窗⼝函数、⼦查询。参考代码如下：

1 -- 写法1
2 SELECT  visit_year,
3        max_visit
4 FROM (
5         SELECT  SUBSTR(data_content, 1, 4) AS visit_year, -- SUBSTR的⽤法
6                 SUBSTR(data_content, -4, 4) AS max_visit,
7                RANK() OVER (PARTITION BY SUBSTR(data_content, 1, 4) ORDER
8                    BY SUBSTR(data_content, -4, 4) DESC) AS ranking,
9         FROM easy_website_visit
10        ORDER BY data_content
11 ) a
12 WHERE a.ranking = 1

8、⽤户购物信息统计

现有两张表，第⼀张表为⽤户在购物⽹站的注册信息表 easy_user_register_info ，该表记录了⽤户在购物⽹站的注册信息，easy_user_register_info 表的数据如下表所⽰：

mysql> SELECT * from easy_user_register_info;
-- user_id（⽤户ID）：VARCHAR register_date（⽤户注册⽇期）：DATE
+---------+---------------+
| user_id | register_date |
+---------+---------------+
| a001    | 2020-10-15    |
| a002    | 2020-11-20    |
| a003    | 2020-12-13    |
| a004    | 2021-01-18    |
+---------+---------------+

另⼀张表为⽤户订单信息表 easy_user_order_info ，easy_user_order_info 表的数据如下所⽰：


mysql> SELECT * FROM easy_user_order_info;
-- user_id（⽤户ID）：VARCHAR order_id（订单ID）：VARCHAR
-- order_date（订单⽇期）：DATE commodity_id（商品ID）：VARCHAR
+---------+----------+------------+--------------+
| user_id | order_id | order_date | commodity_id |
+---------+----------+------------+--------------+
| a001    | o001     | 2020-11-12 | c005          |
| a002    | o002     | 2020-12-27 | c003          |
| a002    | o003     | 2021-01-12 | c003          |
| a003    | o004     | 2021-02-25 | c001          |
| a004    | o005     | 2021-03-12 | c004          |
| a004    | o006     | 2021-03-14 | c005          |
+---------+----------+------------+--------------+

【题⽬8】查询每个⽤户的注册⽇期及其在2021年的订单总数。输出内容包括： user_id（⽤户ID）、 reg_date（注册⽇期）、orders_2021（在2021年的订单总数）

【题⽬8解析】将⽤户订单信息表和注册信息表进⾏LEFT JOIN，即可得到所有订单信（包括⽤户注册⽇期），然后筛选出再2021年有购物订单的⽤户，并分组统计其在2021年的订单总数。涉及知识

点：⼦查询、窗⼝函数。参考代码如下：


1 SELECT aa.user_id,
2        aa.register_date,
3        IFNULL(bb.order_2021, 0) AS orders_2021
4 FROM  easy_user_register_info aa
5 LEFT JOIN 6 (
7     SELECT  b.user_id,
8            b.register_date,
9            COUNT(order_id) AS order_2021
10     FROM easy_user_order_info a
11     LEFT JOIN easy_user_register_info b ON a.user_id = b.user_id
12     WHERE YEAR(order_date) = 2021
13     GROUP BY b.user_id, b.register_date
14 ) bb ON aa.user_id = bb.user_id; 15

9、连续售出的商品

现有⼀张⽤户在电商⽹站的购物订单部分信息表 easy_sold_succession ，该表中的信息是按照时间顺序排列的，easy_sold_succession 表的数据如下所⽰：

mysql> SELECT * FROM easy_sold_succession;
-- order_id（订单ID）：INT commodity_id（购买的商品ID）：VARCHAR
+----------+--------------+
| order_id | commodity_id |
+----------+--------------+
|         1 | c_001     |
|       2 | c_001       |
|       3 | c_002       |
|       4 | c_002       |
|       5 | c_002       |
|       6 | c_001       |
|       7 | c_003       |
|       8 | c_003       |
|       9 | c_003       |
|      10 | c_003       |
|      11 | c_001       |

【题⽬9】找出连续下单⼤于或等于3次的商品ID。输出内容包括：commodity_id（购买的商品ID）

【题⽬9解析】使⽤窗⼝函数LAG(order_id,2)，根据商品ID进⾏分组，并按照订单顺序默认升序延后两⾏展⽰。涉及知识点：⼦查询、窗⼝函数、 DISTINCT。本题的SQL代码如下：

1 SELECT DISTINCT commodity_id
2 FROM
3 (
4     -- 这⾥把order_id 往后移动2个位置得到辅助字段 temp
5     SELECT  commodity_id,
6             order_id,
7      LAG(order_id,2) OVER (PARTITION BY commodity_id ORDER BYorder_id) AS temp
8     FROM easy_sold_succession 9 )a
10 WHERE order_id = temp + 2 --当这个条件满⾜时，⼀定是同⼀个商品⾄少连续3单

10、优惠券使⽤分析

现有⼀张电商优惠券领取表 easy_coupon_collection ，该表记录了⽤户领取优惠券的信息， easy_coupon_collection 表的数据如下表所⽰：

SELECT * FROM easy_coupon_collection;
-- user_id（⽤户ID）：VARCHAR collection_date（领取优惠券⽇期）：DATE
+---------+-----------------+
| user_id | collection_date |
+---------+-----------------+
| u001    | 2021-05-01      |
| u002    | 2021-05-01      |
| u003    | 2021-05-02      |
| u004    | 2021-05-02      |
| u005    | 2021-05-03      |
+---------+-----------------+

还有⼀张电商消费情况表 easy_consumption_info ，easy_consumption_info 表的数据如下表所⽰：

1 SELECT * FROM easy_consumption_info;
2 -- user_id（⽤户ID）：VARCHAR consumption_date（消费⽇期）：DATE
3 +---------+------------------+
4 | user_id | consumption_date |
5 +---------+------------------+
6 | u002    | 2021-04-28       |
7 | u001    | 2021-04-29       |
8 | u001    | 2021-05-03       |
9 | u003    | 2021-05-05       |
10 | u005    | 2021-05-06       |
11 | u001    | 2021-05-08       |
12 | u004    | 2021-05-09       |
13 | u006    | 2021-05-09       |
14 | u003    | 2021-05-10       |
15 | u002    | 2021-05-10       |
16 +---------+------------------+

【题⽬10】⽤户领取的优惠券在次⽇⽣效，并在之后的7天内购物时⾃动⽣效，使⽤次数不限。要求获取成功使⽤消费券消费的⽤户及其对应的消费次数。输出的内容包括： user_id（成功使⽤优惠券消费的⽤户）、num（消费次数）

【题⽬10解析】将两张表使⽤INNER JOIN进⾏连接，连接条件为⽤户ID相同，然后使⽤DATE_DIFF() 函数筛选出符合优惠券使⽤的⽇期，并将筛选出的结果分组计数，即可得到结果。涉及知识点：分组聚合、⽇期/时间处理函数。本题的SQL代码如下：

1 -- 第①种写法
2 SELECT  e1.user_id,
3        COUNT(e2.consumption_date) AS num
4 FROM easy_coupon_collection e1
5 INNER JOIN easy_consumption_info e2 ON e1.user_id = e2.user_id
6 WHERE DATEDIFF(e2.consumption_date, e1.collection_date) BETWEEN 1 AND 7
7 GROUP BY e1.user_id; 8
9 +---------+-----+ 
10 | user_id | num | 
11 +---------+-----+ 
12  | u001    |  2 | 
13  | u003    |  1 | 
14  | u005    |  1 | 
15  | u004    |  1 | 
16 +---------+-----+
17
18
19 -- 第②种写法
20 SELECT  e1.user_id,
21        COUNT(e2.consumption_date) AS num
22 FROM easy_coupon_collection e1
23 INNER JOIN easy_consumption_info e2
24 ON e1.user_id = e2.user_id
25   AND e2.consumption_date BETWEEN DATE_ADD(e1.collection_date, INTERVAL 1 DAY)
26   AND DATE_ADD(e1.collection_date, INTERVAL 7 DAY)
27 GROUP BY e1.user_id; 28
29 +---------+-----+ 
30 | user_id | num |
31 +---------+-----+ 
32  | u001    |  2 | 
33  | u003    |  1 | 
34  | u005    |  1 | 
35  | u004    |  1 | 
36 +---------+-----+

11、商品销量同环⽐

现有—张商品销量表 easy_comparative_analysis ，该表记录了商品每⽉销量信息， easy_comparative_analysis 表数据如下所⽰：

1	SELECT * FROM easy_comparative_analysis;
2	-- month（⽉份）VARCHAR sales_volume（销量）INT
3	+---------+--------------+
4	| month   | sales_volume |
5	+---------+--------------+
6	| 2020-05 |         834 |
7	| 2020-06 |         604 |
8	| 2020-07 |         715 |
9	| 2020-08 |         984 |
10	| 2020-09 |        1024 |
11	| 2020-10 |         893 |
12	| 2020-11 |         485 |
13	| 2020-12 |         890 |
14	| 2021-01 |         563 |
15	| 2021-02 |         301 |
16	| 2021-03 |        1145 |
17	| 2021-04 |        1804 |
18	| 2021-05 |        1493 |

【题⽬11】统计2021年5⽉销量的同环⽐情况。输出内容包括：sales_volume（2021年5⽉销量）、 year_ratio（2021年5⽉同⽐销量）、month_ratio（2021年5⽉环⽐销量）

【题⽬11解析】使⽤LAG()函数分别获取2020年5⽉和2021年4⽉的销量情况作为同环⽐基准，计算同环⽐，通过WHERE筛选将2021年5⽉的同环⽐结果输出即可。涉及知识点：⼦查询、窗⼝函数。本题的SQL代码如下：

1 SELECT  sales_volume4
2         , CONCAT((sales_volume / year_on_year) * 100, '%')   AS year_ratio
3                            -- 同⽐=(去年同期数据/本期数据)×100%
4         , CONCAT((sales_volume / month_on_month) * 100, '%') AS month_ratio
5                            -- 环⽐=(本期统计周期数据/上期统计周期数据)×100%
6 FROM 7 (
8     SELECT  month,
9        sales_volume,
10        LAG(sales_volume, 1) OVER (ORDER BY month)  AS month_on_month,
11        LAG(sales_volume, 12) OVER (ORDER BY month) AS year_on_year,
12                                                  --错位12个⽉即获去年同期
13     FROM easy_comparative_analysis
14 ) a
15 WHERE month = '2021-05';