SQL Interview Questions – Leetcode 512 – Game Play Analysis II

Problem Description –

Write a SQL query that reports the device that is first logged in for each player.

The query result format is in the following example:

Difficulty Level – Easy

Solution –

Method – 1

SELECT
	player_id,
	device_id
FROM (
	 SELECT 
	 	player_id,
	 	device_id,
	 	event_date,
	 	MIN(event_date) OVER(PARTITION BY player_id ORDER BY event_date) as first_login
	 FROM Activity
 	) t1
 WHERE event_date = first_login

Here, we are using the MIN() window function to get the first login date for each player. We are partitioning the data by each player Id so that we get a separate first login date for each user and also ordering the event_date in ascending order to get the earliest date when the user first logged In.

Then in the outer query, we are filtering the data WHERE event_date = first_login date, which makes sure that we only get the rows where this condition is true and in the select clause we are just selecting the player_id and device_id as asked in the question.

Method 2 –

SELECT
    player_id,
    device_id
FROM Activity
WHERE (player_id, event_date) IN (SELECT
                                        player_id,
                                        MIN(event_date) 
                                    FROM Activity 
                                    GROUP BY player_id)

In this method, first, we are selecting each player’s id and their first login date using the MIN() function. Which will give us the following result.

Then in the outer WHERE clause, we are filtering only the rows from the Activity table where player_id and event_date match the values from the inner subquery table shown above.