【面试题】
某游戏的某促销活动，会向玩家推荐一个道具，同时会得到该道具的折扣券。折扣券无有效期，但购买道具一次后失效。推荐一个新的道具，也会导致旧的折扣券失效。
假设道具推荐、查看、购买行为记录在了下面的“游戏道具记录表”中，包含4个字段：时间、玩家id、行为、道具id。
表中的“行为”字段的值包括：向玩家推荐道具、玩家查看道具、玩家成功付费购买道具
数据说明：
1）一个道具的折扣券失效后，玩家仍然可以以原价购买推荐的道具
2）同一个道具不会被重复推荐
3）如果玩家使用折扣券购买道具，则认为这次推荐道具->查看道具->购买道具属于一个成功推荐过程，推荐道具->购买道具（即中途缺少该道具的查看记录）不属于成功推荐过程。
4）玩家可以推荐前查看道具，但不会获得折扣券
【问题】查询所有成功推荐过程中，该道具的第一条查看记录。
【解题步骤】
1. 解题思路
这个业务问题一看很复杂，遇到复杂的问题，要想到用逻辑树分析方法，将复杂问题拆解为可以解决的子问题：
1）找出所有成功推荐的记录（成功推荐是玩家使用折扣券进行购买）
2）找出成功推荐时间与对应购买时间之间的查看记录
3）筛选出每一次成功推荐的第一条查看记录
2. 所有成功推荐的记录
一次成功推荐是“推荐道具->查看道具->购买道具”这样的过程。
1）获取所有推荐道具的数据
select 时间,玩家id,行为,道具idfrom 游戏道具记录表where 行为 = '推荐道具';
记为表t1。
2）获取所有购买道具的数据
select 时间,玩家id,行为,道具idfrom 游戏道具记录表where 行为 = '玩家购买';
记为表t2。
3）获取同一个玩家同一个道具的“推荐道具->购买道具”
以“所有推荐道具的数据”为左表，使用左联结，从“所有购买道具的数据”中获取同一个玩家同一个道具的购买记录（购买时间在推荐时间之后）。
由于一次推荐之后，可能存在多次购买记录，此处使用窗口函数每次推荐之后对应的购买时间顺序。
select t1.玩家id,t1.时间 as 推荐道具时间,t1.道具id as 推荐道具id,t2.时间 as 玩家购买时间,t2.道具id as 玩家购买道具id,row_number() over(partition by t1.玩家id,t1.时间,t1.道具id order by t2.时间) as 玩家购买时间顺序from t1left joint2 on t1.玩家id = t2.玩家id and t1.道具id = t2.道具idwhere t1.时间 < t2.时间;
查询结果：
以上结果记为表t3。
4）对“玩家购买时间顺序”进行筛选
按照成功推荐的定义，“玩家购买时间顺序”为1时，才可能成为成功推荐。
因为，购买道具一次后道具对应的折扣券会失效。
select 玩家id,推荐道具时间,推荐道具id,玩家购买时间,玩家购买道具idfrom t3where 玩家购买时间顺序 = 1;
以上结果记为表tt1。
5）得到成功推荐的记录
上一步的结果还不是成功推荐。
因为，如果“推荐道具时间”与“玩家购买时间”之间有另外的一次推荐，这次的推荐的折扣券就失效了。
查询出“推荐道具时间”与“玩家购买时间”之间没有其他推荐的记录。
select tt1.玩家id,tt1.推荐道具时间,tt1.推荐道具id,tt1.玩家购买时间,tt1.玩家购买道具idfrom tt1left join t1 on tt1.玩家id = t1.玩家id and tt1.推荐道具时间 < t1.时间 and tt1.玩家购买时间 > t1.时间where t1.行为 is null;
以上结果记为数据a1。
3. 获取成功推荐中的查看记录
1）获取所有查看道具的数据
select 时间,玩家id,行为,道具idfrom 游戏道具记录表where 行为 = '玩家查看';
记为表a2。
2）获取成功推荐记录中“推荐道具时间”与“玩家购买时间”之间的所有查看记录
select a1.玩家id,a1.推荐道具时间,a1.推荐道具id,a1.玩家购买时间,a1.玩家购买道具id,a2.时间 as 玩家查看时间,a2.道具id as 玩家查看道具id,row_number() over(partition by a1.玩家id,a1.玩家购买时间,a1.玩家购买道具id order by a2.时间) as 查看时间顺序from a1left joina2 on a1.玩家id = a2.玩家id and a1.推荐道具id = a2.道具idwhere a2.时间 > a1.推荐道具时间 and a2.时间 <= a1.玩家购买时间;
同时，用窗口函数row_number()获取了“查看时间顺序”。
以上结果记为a3。
4. 获取成功推荐中的第一条查看记录
最后，筛选出第一次查看的记录，并恢复成初始的数据结构。
select 玩家查看时间 as 时间,玩家id,'玩家查看' as 行为,玩家查看道具id as 道具idfrom a3where 查看时间顺序 = 1;
将子查询代入：
select 玩家查看时间 as 时间,玩家id,'玩家查看' as 行为,玩家查看道具id as 道具idfrom (select a1.玩家id,a1.推荐道具时间,a1.推荐道具id,a1.玩家购买时间,a1.玩家购买道具id,a2.时间 as 玩家查看时间,a2.道具id as 玩家查看道具id,row_number() over(partition by a1.玩家id,a1.玩家购买时间,a1.玩家购买道具id order by a2.时间) as 查看时间顺序from (select tt1.玩家id,tt1.推荐道具时间,tt1.推荐道具id,tt1.玩家购买时间,tt1.玩家购买道具idfrom (select 玩家id,推荐道具时间,推荐道具id,玩家购买时间,玩家购买道具idfrom (select t1.玩家id,t1.时间 as 推荐道具时间,t1.道具id as 推荐道具id,t2.时间 as 玩家购买时间,t2.道具id as 玩家购买道具id,row_number() over(partition by t1.玩家id,t1.时间,t1.道具id order by t2.时间) as 玩家购买时间顺序from (select 时间,玩家id,行为,道具idfrom 游戏道具记录表where 行为 = '推荐道具') as t1left join (select 时间,玩家id,行为,道具idfrom 游戏道具记录表where 行为 = '玩家购买') as t2 on t1.玩家id = t2.玩家id and t1.道具id = t2.道具idwhere t1.时间 < t2.时间) as t3where 玩家购买时间顺序 = 1) as tt1left join (select 时间,玩家id,行为,道具idfrom 游戏道具记录表where 行为 = '推荐道具') as tt2 on tt1.玩家id = tt2.玩家id and tt1.推荐道具时间 < tt2.时间 and tt1.玩家购买时间 > tt2.时间where tt2.行为 is null) as a1left join (select 时间,玩家id,行为,道具idfrom 游戏道具记录表where 行为 = '玩家查看') as a2 on a1.玩家id = a2.玩家id and a1.推荐道具id = a2.道具idwhere a2.时间 > a1.推荐道具时间 and a2.时间 <= a1.玩家购买时间) as a3where 查看时间顺序 = 1;
【本题考点】
1）考查对窗口函数的了解；
2）考查对多表联结的了解。
⬇️点击「阅读原文」
免费报名 数据分析训练营
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
