# A simple solution

• ``````SELECT s1.* FROM stadium AS s1, stadium AS s2, stadium as s3
WHERE
((s1.id + 1 = s2.id
AND s1.id + 2 = s3.id)
OR
(s1.id - 1 = s2.id
AND s1.id + 1 = s3.id)
OR
(s1.id - 2 = s2.id
AND s1.id - 1 = s3.id)
)
AND s1.people>=100
AND s2.people>=100
AND s3.people>=100

GROUP BY s1.id
``````

• neat! one question: why we need to "group by s1.id"? thank you:)

• @shaniavina I think it's because if you join table in this way, there're will be duplicate rows in the table.

You can also do in this way,

SELECT Distinct s1.id, s1.date, s1.people
WHERE ((s1.id-1= s2.id and s1.id-2 = s3.id)
Or (s1.id+1 = s2.id and s1.id + 2= s3.id)
Or (s1.id-1 = s2.id and s1.id+1 = s3.id))
and s1.people>=100 and s2.people>=100 and s3.people>=100
ORDER BY s1.id;

• Exactly. :) .

• Can you please explain why it can also handle cases when n > 3?

• @cimyntt
If there are n>3 days, say we have n = 4.

Notice we have three OR conditions,
either s1 being the first day (`s1.id + 1 = s2.id AND s1.id + 2 = s3.id)`,
second day (`s1.id - 1 = s2.id AND s1.id + 1 = s3.id`)
or the third day (`s1.id - 2 = s2.id AND s1.id - 1 = s3.id`).
So three consecutive days meet the requirements would show up.

But this will produce duplicate results, for example in 4 consecutive days, there would be two days overlapping, which would pop out twice. In this way, the `group by` would eliminate the duplicate records.

• What's the runtime on this? I used a similar logic but with six sub-queries and one main query and got near 400ms (top 25%).

I'm preparing for an interview and trying to understand the relative differences in the efficiency of self-joins vs. sub-queries.

``````SELECT id, date, people
WHERE people >= 100
AND (SELECT people FROM stadium b WHERE b.id = a.id + 1) >= 100
AND (SELECT people FROM stadium c WHERE c.id = a.id + 2) >= 100
OR
people >= 100
AND (SELECT people FROM stadium e WHERE e.id = a.id - 1) >= 100
AND (SELECT people FROM stadium f WHERE f.id = a.id + 1) >= 100
OR
people >= 100
AND (SELECT people FROM stadium g WHERE g.id = a.id - 1) >= 100
AND (SELECT people FROM stadium h WHERE h.id = a.id - 2) >= 100
``````

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.