LeetCode-Solutions/MySQL/find-interview-candidates.sql at master · hackto-dev/LeetCode-Solutions · GitHub

31 lines (29 loc) · 820 Bytes

# Time: O(nlogn)

# Space: O(n)

WITH winners_cte AS

((SELECT gold_medal AS winner, contest_id

FROM contests)

UNION ALL

(SELECT silver_medal AS winner, contest_id

FROM contests)

UNION ALL

(SELECT bronze_medal AS winner, contest_id

FROM contests)),

consecutive_winners_cte AS

(SELECT winner, contest_id, row_number() over(PARTITION BY winner ORDER BY contest_id) AS row_num

FROM winners_cte),

candidates_cte AS

((SELECT winner AS user_id

FROM consecutive_winners_cte

GROUP BY winner, contest_id - row_num

HAVING count(1) >= 3

ORDER BY NULL)

UNION

(SELECT gold_medal AS user_id

FROM contests

GROUP BY gold_medal

HAVING count(1) >= 3

ORDER BY NULL))

SELECT u.name, u.mail

FROM users u

INNER JOIN candidates_cte c ON u.user_id = c.user_id;

Expand file tree