LeetCode 601. Human Traffic of Stadium SQL Solution

Problem

LeetCode SQL Problem

601. Human Traffic of Stadium

Stadium table

id	visit_date	people
1	2017-01-01T00:00:00Z	10
2	2017-01-02T00:00:00Z	109
3	2017-01-03T00:00:00Z	150
4	2017-01-04T00:00:00Z	99
5	2017-01-05T00:00:00Z	145
6	2017-01-06T00:00:00Z	1455
7	2017-01-07T00:00:00Z	199
8	2017-01-09T00:00:00Z	188

Solution - Row_Number Approach

• Firstable, we filter out stadium visits that have less than 100 people and use Row_Number() window function to assign a sequential row_num integer to each row within the result set.
• Calculate the difference between id and row_num to identify consecutive id's. Consecutive id's will be assigned the same island_number.
• Use GROUP BY and HAVING to calculate the size of each island and find out which island has three or more consecutive id's.
• INNER JOIN islands and big_enough_islands on island_number to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each.

MySQL

WITH busy_visits
AS (
	SELECT *,
		row_number() OVER (
			ORDER BY s.id
			) AS row_num
	FROM Stadium s
	WHERE s.people >= 100
	),
islands
AS (
	SELECT *,
		bv.id - bv.row_num AS island_number
	FROM busy_visits bv
	),
big_enough_islands
AS (
	SELECT ii.island_number,
		count(*) AS island_size
	FROM islands ii
	GROUP BY ii.island_number
	HAVING island_size >= 3
	)
SELECT i.id,
	i.visit_date,
	i.people
FROM islands i
INNER JOIN big_enough_islands bei ON i.island_number = bei.island_number
ORDER BY i.visit_date;

TSQL

WITH busy_visits
AS (
	SELECT *,
		row_number() OVER (
			ORDER BY s.id
			) AS row_num
	FROM Stadium s
	WHERE s.people >= 100
	),
islands
AS (
	SELECT *,
		bv.id - bv.row_num AS island_number
	FROM busy_visits bv
	),
big_enough_islands
AS (
	SELECT ii.island_number,
		count(*) AS island_size
	FROM islands ii
	GROUP BY ii.island_number
	HAVING count(*) >= 3
	)
SELECT i.id,
	i.visit_date,
	i.people
FROM islands i
INNER JOIN big_enough_islands bei ON i.island_number = bei.island_number
ORDER BY i.visit_date;

Query Output

id	visit_date	people
5	2017-01-05T00:00:00Z	145
6	2017-01-06T00:00:00Z	1455
7	2017-01-07T00:00:00Z	199
8	2017-01-09T00:00:00Z	188

Solution - Cross Join Approach

• CROSS JOIN Stadium table 3 times to generate all combinations of 3 id's
• Only include combinations that have 3 consecutive id's and the number of people is greater than or equal to 100 for each
• Use DISTINCT keyword to remove duplicate rows

MySQL

SELECT DISTINCT t1.*
FROM Stadium t1
CROSS JOIN Stadium t2
CROSS JOIN Stadium t3
WHERE (t1.people >= 100)
	AND (t2.people >= 100)
	AND (t3.people >= 100)
	AND (
		(
			-- Check previous 2 numbers of t1
			-- t3, t2, t1
			t1.id - t2.id = 1
			AND t1.id - t3.id = 2
			AND t2.id - t3.id = 1
			)
		OR (
			-- Check previous number and next number of t1
			-- t3, t1, t2
			t2.id - t1.id = 1
			AND t2.id - t3.id = 2
			AND t1.id - t3.id = 1
			)
		OR (
			-- Check next 2 numbers of t1
			-- t1, t2, t3
			t3.id - t2.id = 1
			AND t2.id - t1.id = 1
			AND t3.id - t1.id = 2
			)
		)
ORDER BY t1.id

TSQL

SELECT DISTINCT t1.*
FROM Stadium t1
CROSS JOIN Stadium t2
CROSS JOIN Stadium t3
WHERE (t1.people >= 100)
	AND (t2.people >= 100)
	AND (t3.people >= 100)
	AND (
		(
			-- Check previous 2 numbers of t1
			-- t3, t2, t1
			t1.id - t2.id = 1
			AND t1.id - t3.id = 2
			AND t2.id - t3.id = 1
			)
		OR (
			-- Check previous number and next number of t1
			-- t3, t1, t2
			t2.id - t1.id = 1
			AND t2.id - t3.id = 2
			AND t1.id - t3.id = 1
			)
		OR (
			-- Check next 2 numbers of t1
			-- t1, t2, t3
			t3.id - t2.id = 1
			AND t2.id - t1.id = 1
			AND t3.id - t1.id = 2
			)
		)
ORDER BY t1.id

Query Output

id	visit_date	people
5	2017-01-05T00:00:00Z	145
6	2017-01-06T00:00:00Z	1455
7	2017-01-07T00:00:00Z	199
8	2017-01-09T00:00:00Z	188