Human Traffic of Stadium
Problem
Stadium(id, visit_date, people). Return rows with people ≥ 100 that participate in three consecutive ids.
s=[(1,'D1',10),(2,'D2',109),(3,'D3',150),(4,'D4',99),(5,'D5',145),(6,'D6',1455),(7,'D7',199),(8,'D8',188)][(5,...),(6,...),(7,...),(8,...)]SELECT DISTINCT s1.*
FROM Stadium s1, Stadium s2, Stadium s3
WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100
AND ((s1.id - s2.id IN (-1,1,2) AND s2.id - s3.id IN (-1,1,2)) /* etc. */)
ORDER BY s1.id;Explanation
We want every busy day (people >= 100) that belongs to a run of three or more consecutive ids that are all busy. The classic SQL way is to compare three copies of the table at once with a self-join.
We bring in three aliases s1, s2, s3 and require all three to have people >= 100. The id conditions then check that the three rows form a block of consecutive ids in some order — for example s2, s3 being the neighbors of s1, or s1 sitting in the middle, and so on.
Any row that can be part of at least one valid triple gets selected. Because the same row can appear in several triples, SELECT DISTINCT removes duplicates, and ORDER BY s1.id sorts the result.
Example: ids 5,6,7,8 are all >= 100. They form the triples (5,6,7) and (6,7,8), so all four rows qualify, while the lone busy day at id 2 has no two busy neighbors and is dropped.