Game Play Analysis Iv - Leetcode Solution

The Game Play Analysis IV problem on Leetcode is a SQL problem that requires us to find the number of players who played at least one game but haven't logged in for 30 days or more consecutively. We are given a table with the following columns:

• player_id: A unique id for each player.
• device_id: A unique id for each device used by a player.
• event_date: The date when the game was played or logged in.
• games_played: The number of games played by the player on the given date.

We need to write a SQL query to solve this problem. Here is the solution:

SELECT COUNT(DISTINCT player_id) as `inactive_players`
FROM
(SELECT 
 player_id, 
 MAX(event_date) as `last_event_date`
 FROM Activity 
 GROUP BY player_id) as `last_event`
WHERE last_event.last_event_date <= DATE_SUB(NOW(), INTERVAL 30 DAY);

Explanation:

We start by selecting all the distinct player ids and their latest event date using the MAX function. We group the results by player_id to get the latest event date of each player. We call this sub-query "last_event".

SELECT 
 player_id, 
 MAX(event_date) as `last_event_date`
 FROM Activity 
 GROUP BY player_id

Next, we select all the player ids from the "last_event" sub-query where the "last_event_date" is 30 days or more before the current date. We use the DATE_SUB() function to subtract 30 days from the current date.

WHERE last_event.last_event_date <= DATE_SUB(NOW(), INTERVAL 30 DAY);

Finally, we count the number of distinct player ids that meet the above criteria and output the result with the alias name "inactive_players".

SELECT COUNT(DISTINCT player_id) as `inactive_players`

In summary, the above SQL query returns the count of players who played at least one game but haven't logged in for 30 days or more consecutively.

1