SQL Interview Questions – Leetcode 550 – Game Play Analysis IV

Problem Description –

Write an SQL query that reports the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places. In other words, you need to count the number of players that logged in for at least two consecutive days starting from their first login date, then divide that number by the total number of players.

The query result format is in the following example:

Difficulty Level – Medium

Solution –

WITH cte AS (
SELECT player_id, MIN(event_date) as first_login
FROM Activity
GROUP BY player_id
)

SELECT ROUND(SUM(CASE WHEN DATEDIFF(event_date, first_login)=1 THEN 1 ELSE 0  END) / COUNT(DISTINCT cte.player_id), 2) as fraction
FROM Activity as a
JOIN cte 
ON a.player_id = cte.player_id

To solve the problem, first, we are creating a cte that gives us the first login date for each player.

Then we are combining this table with the Activity table on the players_id column. Which will give us the following table.

WITH cte AS (
	SELECT
		player_id,
		MIN(event_date) AS first_login
	FROM
		Activity
	GROUP BY
		player_id
)

SELECT a.*, cte.*
FROM Activity As a 
JOIN cte ON a.player_id = cte.player_id

Once we have this table we can use a CASE statement to find the difference between the event date and first_login. If this difference is 1 then we will assign 1 else we will assign 0.

WITH cte AS (
	SELECT
		player_id,
		MIN(event_date) AS first_login
	FROM
		Activity
	GROUP BY
		player_id
)

SELECT a.*, cte.*,
	(CASE WHEN DATEDIFF(event_date, first_login) = 1 THEN 1 ELSE 0 END) as case_col
FROM Activity As a 
JOIN cte ON a.player_id = cte.player_id

Now, when we use the SUM() function, in the numerator, we will get 1, and this statement in the denominator COUNT( DISTINCT cte.player_id ) will give us 3 as we have three unique players which ultimately will be 1/3 = 0.33, the answer that we are looking for. We also rounded the division of 1/3 to two decimal places to get 0.33.