Problem Description –
Write an SQL query to report the
name and the
- 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
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