LeetCode---游戏

Activity table:
+-----------+-----------+------------+--------------+
| 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            |
+-----------+-----------+------------+--------------+

（player_id，event_date）是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）

 

 

1.请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称

Result table:
+-----------+-----------+
| player_id | device_id |
+-----------+-----------+
| 1         | 2         |
| 2         | 3         |
| 3         | 1         |
+-----------+-----------+

---解法一,搜索的这两列是主键
select player_id,device_id
from Activity
where (player_id,device_id) in
(select player_id,min(event_date) from Activity group by player_id)

---解法二
select b.player_id,b.device_id
from (select player_id,device_id,dense_rank()over(partition by player_id order by event_date)rk from Activity) b
where b.rk=1;

---解法三
select player_id,device_id
from(
select player_id,
case when @player=player_id then @num:=@num+1
when @player:=player_id then @num:=1 end as num,device_id
from Activity a,(select  @player:=0,@num:=0)b order by player_id, a.event_date asc )where t.num =1;

2.编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。

Result table:
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
+-----------+------------+---------------------+

--解法一,直接用sum
select player_id,event_date,sum(games_played)over(partition by player_id order by event_date)event_date
from Activity

--解法二,用自连接
select t1.player_id,t2.event_date,sum(t2.games_played) as games_played_so_far
from Activity t1 left join Activity t2
on t1.player_id=t2.player_id and t1.event_date>=t2.event_date
group by t1.player_id,t2.event_date;

3.编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的分数，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

Result table:
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+

 注意是首次登陆

---解法一
select round(
    (
        select count(distinct player_id) as second_login from Activity a 
   
        left join

        (
         select player_id,min(event_date) as first_date)
         from Activity 
         group by player_id
        )t
         on a.player_id=t.player_id
         where datediff(event_date,first_date)=1
    )
         /(
           select count(distinct player_id) as total_player_num from Activity
          ),2
)as fraction


---解法二
select round(
       count(distinct player_id)
       /(select count(distinct player_id) as total_player_num  
         from Activity
        ),2 ) as fraction
        from Activity
        where (player_id,event_date) in
        (
        select player_id,date(min(event_date)+1) from Activity
        group by player_id
        )