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.