-- Table: Activity
-- +--------------+---------+
-- | Column Name | Type |
-- +--------------+---------+
-- | player_id | int |
-- | device_id | int |
-- | event_date | date |
-- | games_played | int |
-- +--------------+---------+
-- (player_id:用户id, event_date:事件id) 是这个表的主键.
-- device_id 是登录的设备号
-- 每一行数据记录的是某个用户,在某个时间点登录了平台,玩了游戏在某个设备上
-- Input:
-- 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 |
-- +-----------+-----------+------------+--------------+
-- Output:
-- +-----------+-----------+
-- | player_id | device_id |
-- +-----------+-----------+
-- | 1 | 2 |
-- | 2 | 3 |
-- | 3 | 1 |
-- +-----------+-----------+
drop table Activity;
Create table If Not Exists Activity
(
player_id int,
device_id int,
event_date date,
games_played int
) row format delimited fields terminated by ',';
insert into Activity (player_id, device_id, event_date, games_played)
values ('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');
1,请编写一个SQL,用于查询每个玩家第一次登录游戏平台,用的那个设备。
2,编写一个SQL查询,同时报告每组玩家和日期,以及玩家到目前为止玩了多少游戏。也就是说,在此日期之前玩家所玩的游戏总数。
-- 对于 ID 为 1 的玩家,2016-03-01 共玩了5款游戏,2016-05-02 共玩了 5+6=11 个游戏,
-- 对于 ID 为 2 的玩家,2017-06-25 共玩了 1 个游戏。
-- 对于 ID 为 3 的玩家,2016-03-02 共玩了 0 个游戏。2018-07-03 共玩了 0+5=5 个游戏。
-- 请注意,对于每个玩家,我们只关心玩家的登录日期。
-- Result table:
-- +-----------+------------+---------------------+
-- | 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 |
-- +-----------+------------+---------------------+
3,编写一个 SQL 查询,报告在首次登录的第二天再次登录的玩家的比率,四舍五入到小数点后两位。换句话说,您需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。
您的答案
1.
select player_id,device_id from (
select player_id,
device_id,
event_date,
row_number() over (partition by player_id order by event_date) fn
from Activity ) A
where A.fn =1;
2.
select player_id,
event_date,
sum(games_played)
over (
partition by player_id order by event_date
rows between unbounded preceding and current row
) games_played_so_far
from Activity;
3.
select count(*)/(select count(player_id) from Activity) count
from (
select player_id,
event_date,
lead(event_date, 1) over (partition by player_id order by event_date) lead
from Activity) A
where A.lead is not null ;