【MySQL】LeetCode 534&550

mysql> select * from activity
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date   | games_played |
+-----------+-----------+------------+--------------+
|         1 |         2 | 2016-03-01 |            5 |
|         1 |         2 | 2016-05-02 |            6 |
|         2 |         3 | 2017-06-25 |            1 |
|         3 |         1 | 2016-03-02 |            0 |
|         3 |         4 | 2018-07-03 |            5 |
+-----------+-----------+------------+--------------+
5 rows in set (0.00 sec)

534 游戏玩法分析3

查询玩家在该日期之前所玩的游戏场数
mysql> select player_id,event_date,sum(games_played)over(partition by player_id order by event_date) as games_played_so_far
    -> from activity
    -> order by player_id,event_date;
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
|         1 | 2016-03-01 |                   5 |
|         1 | 2016-05-02 |                  11 |
|         2 | 2017-06-25 |                   1 |
|         3 | 2016-03-02 |                   0 |
|         3 | 2018-07-03 |                   5 |
+-----------+------------+---------------------+
5 rows in set (0.00 sec)

550 游戏玩法分析4

mysql> select * from activity
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date   | games_played |
+-----------+-----------+------------+--------------+
|         1 |         2 | 2016-03-01 |            5 |
|         1 |         2 | 2016-03-02 |            6 |
|         2 |         3 | 2017-06-25 |            1 |
|         3 |         1 | 2016-03-02 |            0 |
|         3 |         4 | 2018-07-03 |            5 |
+-----------+-----------+------------+--------------+
5 rows in set (0.00 sec)

mysql> select round(count(distinct player_id)/(select count(distinct player_id)from activity ),2) as fraction
    -> from( select player_id,event_date,
    -> lead(event_date) over(partition by player_id)as next_log_date
    -> from activity ) T 
    -> where next_log_date is not null and datediff(next_log_date,event_date)=1;
+----------+
| fraction |
+----------+
|     0.33 |
+----------+
1 row in set (0.00 sec)