LeetCode 550. Game Play Analysis IV SQL Solution

Problem

LeetCode SQL Problem

550. Game Play Analysis IV

Activity table

player_id	device_id	event_date	games_played
1	2	2016-03-01	5
1	2	2016-03-02	6
2	3	2017-06-25	1
3	1	2016-03-02	0
3	4	2018-07-03	5

Solution

MySQL

Game Play Analysis IV

-- Get each player's first login date
WITH PlayerFirstLogin
AS (
	SELECT player_id
		,min(event_date) AS first_login_date
	FROM Activity
	GROUP BY player_id
	)
	,PlayerConsecutiveLogin
-- Get players players that logged in for at least two consecutive days starting from their first login date
AS (
	SELECT A.player_id
	FROM Activity AS A
	INNER JOIN PlayerFirstLogin FL ON A.player_id = FL.player_id
		AND datediff(A.event_date, FL.first_login_date) = 1
	)
-- Report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.
SELECT cast((
			SELECT count(DISTINCT player_id)
			FROM PlayerConsecutiveLogin
			) / cast((
				SELECT count(DISTINCT player_id)
				FROM Activity
				) AS FLOAT) AS DECIMAL(10, 2)) AS fraction

TSQL

Game Play Analysis IV

-- Get each player's first login date
WITH PlayerFirstLogin
AS (
	SELECT player_id
		,min(event_date) AS first_login_date
	FROM Activity
	GROUP BY player_id
	)
-- Get players players that logged in for at least two consecutive days starting from their first login date
	,PlayerConsecutiveLogin
AS (
	SELECT A.player_id
	FROM Activity AS A
	INNER JOIN PlayerFirstLogin FL ON A.player_id = FL.player_id
		AND datediff(DAY, FL.first_login_date, A.event_date) = 1
	)
-- Report the fraction of players that logged in again on the day after the day they first logged in, rounded to 2 decimal places.
SELECT cast((
			SELECT count(DISTINCT player_id)
			FROM PlayerConsecutiveLogin
			) / cast((
				SELECT count(DISTINCT player_id)
				FROM Activity
				) AS FLOAT) AS DECIMAL(10, 2)) AS fraction

Query Output

fraction
0.33