# 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:

### Solution –

``````WITH cte AS (
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,
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,
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.

Rating: 1 out of 5.