masteranalytics Uncategorized

Leetcode SQL (#511, #512, #534, #550)

511. Game Play Analysis I

Question:

https://leetcode.com/problems/game-play-analysis-i/

Solution:

select 
player_id, 
min(event_date) as first_login
from Activity
group by 1
order by 1

512. Game Play Analysis II

Question:

https://leetcode.com/problems/game-play-analysis-ii/

Solution:

select 
player_id, 
device_id
from(
    select 
    player_id, 
    device_id, 
    row_number() over(partition by player_id order by event_date) as r
    from Activity
) a
where r = 1

534. Game Play Analysis III

Question:

https://leetcode.com/problems/game-play-analysis-iii/

Solution:

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 1,2

550. Game Play Analysis IV

Question:

https://leetcode.com/problems/game-play-analysis-iv/

Solution:

select 
round(count(distinct a.player_id)/ (select count(distinct player_id) from Activity),2) as fraction
from (
    select 
    player_id, 
    event_date,
    lead(event_date,1) over(partition by player_id order by event_date) as next_event_date
    from Activity
    ) a
join (
    select 
    player_id, 
    min(event_date) as min_event_date 
    from Activity 
    group by 1
) m 
    on a.player_id = m.player_id 
    and a.event_date = m.min_event_date
where datediff(a.next_event_date, a.event_date) = 1