LeetCode-511/512/534. 游戏玩法分析 I/II/III

511. 游戏玩法分析 I

本人思路：
1、分组即可

select player_id,min(event_date)as first_login
from activity 
group by player_id

512. 游戏玩法分析 II

本人思路:
– 1、先找到每个用户首次登陆的日期(min)，构成临时表temp
– 2、表activity 内连接表temp，找到设备的属性即可

select a1.player_id, a1.device_id
from activity as a1 inner join
(
select player_id,min(event_date)as min_date
from activity 
group by player_id
)temp
on a1.player_id = temp.player_id 
and a1.event_date = temp.min_date

534. 游戏玩法分析 III

本人思路：
– 1、累积问题解法 ，可以使用笛卡尔积连接，条件是a1.player_id = a2.player_id and a1.event_date >= a2.event_date，这样可以找到每个用户在此日期前有几次登陆日期
– 2、再利用日期分组来统计玩游戏的总数

select a1.player_id,a1.event_date, sum(a2.games_played)as games_played_so_far 
from activity as a1 ,activity as a2 
where a1.player_id = a2.player_id and a1.event_date >= a2.event_date
group by a1.player_id,a1.event_date
order by a1.player_id,a1.event_date desc

题目来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/game-play-analysis-iii
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。