LeetCode 512. Game Play Analysis II SQL Solution

Problem

LeetCode SQL Problem

512. Game Play Analysis II

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

Solution - Group By + Subquery

MySQL

Report the device that is first logged in for each player

-- Use a subquery to report the first login date for each player.
-- For first login records, find out the device_id of each player_id
SELECT
  player_id,
  device_id
FROM
  Activity
WHERE
  (player_id, event_date) IN (
    SELECT
      player_id,
      min(event_date) AS first_login
    FROM
      Activity
    GROUP BY
      player_id
  )

TSQL

Report the device that is first logged in for each player

-- Figure out each player's first login date and store results temporarily in CTE
WITH PlayerFirstLoginDate
AS (
	SELECT player_id
		,min(event_date) AS first_login_date
	FROM Activity
	GROUP BY player_id
	)
-- Perform an INNER JOIN to ONLY return each player's first-login record
SELECT A.player_id
	,A.device_id
FROM Activity AS A
INNER JOIN PlayerFirstLoginDate AS PFLD ON A.player_id = PFLD.player_id
	AND A.event_date = PFLD.first_login_date

Query Output

player_id	device_id
1	2
2	3
3	1

Solution - First_Value() Window Function

MySQL

Report the device that is first logged in for each player

-- Use First_Value() Window Function to figure out the first device_id for each player_id
-- Use DISTINCT keyword to return unique player_id and device_id combination
WITH PlayerFirstDevice
AS (
	SELECT *
		,first_value(device_id) OVER (
			PARTITION BY player_id ORDER BY event_date ASC
			) AS first_device_id
	FROM Activity
	)
SELECT DISTINCT player_id
	,first_device_id AS device_id
FROM PlayerFirstDevice

TSQL

Report the device that is first logged in for each player

-- Use First_Value() Window Function to figure out the first device_id for each player_id
-- Use DISTINCT keyword to return unique player_id and device_id combination
WITH PlayerFirstDevice
AS (
	SELECT *
		,first_value(device_id) OVER (
			PARTITION BY player_id ORDER BY event_date ASC
			) AS first_device_id
	FROM Activity
	)
SELECT DISTINCT player_id
	,first_device_id AS device_id
FROM PlayerFirstDevice

Query Output

player_id	device_id
1	2
2	3
3	1