Problem Description –
Write an SQL query to report the name
and the mail
of all interview candidates. A user is an interview candidate if at least one of these two conditions is true:
- The user won any medal in three or more consecutive contests.
- The user won the gold medal in three or more different contests (not necessarily consecutive).
Return the result table in any order.
The query result format is in the following example.


Problem Link – Find interview candidates
Difficulty Level – Medium
Solution –
with t0 as (
select gold_medal as user, contest_id
from contests
union all
select silver_medal as user, contest_id
from contests
union all
select bronze_medal as user, contest_id
from contests
)
, t1 as (
select user, contest_id, row_number() over(partition by user order by contest_id) as rn
from t0
)
, t2 as (
select user as user_id -- consecutive medal winners
from t1
group by user, contest_id - rn
having count(*) >= 3 -- replace 3 with any number to solve the N problem
union all
select gold_medal as user_id -- gold medal winners
from contests
group by gold_medal
having count(*) >= 3
)
select distinct u.name, u.mail
from t2
inner join users u
on t2.user_id = u.user_id