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
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.