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
FROM stadium s1, stadium s2, stadium s3
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;
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.
Hope this help you.
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 FROM stadium a 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.