> For the complete documentation index, see [llms.txt](https://zqt0.gitbook.io/leetcode/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://zqt0.gitbook.io/leetcode/sql/1811.find-interview-candidates.md). # 1811.(Medium)Find Interview Candidates Table: Contests ``` +--------------+------+ | Column Name | Type | +--------------+------+ | contest_id | int | | gold_medal | int | | silver_medal | int | | bronze_medal | int | +--------------+------+ contest_id is the primary key for this table. This table contains the LeetCode contest ID and the user IDs of the gold, silver, and bronze medalists. It is guaranteed that any consecutive contests have consecutive IDs and that no ID is skipped. ``` Table: Users ``` +-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | mail | varchar | | name | varchar | +-------------+---------+ user_id is the primary key for this table. This table contains information about the users. ``` 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: ``` Contests table: +------------+------------+--------------+--------------+ | contest_id | gold_medal | silver_medal | bronze_medal | +------------+------------+--------------+--------------+ | 190 | 1 | 5 | 2 | | 191 | 2 | 3 | 5 | | 192 | 5 | 2 | 3 | | 193 | 1 | 3 | 5 | | 194 | 4 | 5 | 2 | | 195 | 4 | 2 | 1 | | 196 | 1 | 5 | 2 | +------------+------------+--------------+--------------+ Users table: +---------+--------------------+-------+ | user_id | mail | name | +---------+--------------------+-------+ | 1 | sarah@leetcode.com | Sarah | | 2 | bob@leetcode.com | Bob | | 3 | alice@leetcode.com | Alice | | 4 | hercy@leetcode.com | Hercy | | 5 | quarz@leetcode.com | Quarz | +---------+--------------------+-------+ Result table: +-------+--------------------+ | name | mail | +-------+--------------------+ | Sarah | sarah@leetcode.com | | Bob | bob@leetcode.com | | Alice | alice@leetcode.com | | Quarz | quarz@leetcode.com | +-------+--------------------+ Sarah won 3 gold medals (190, 193, and 196), so we include her in the result table. Bob won a medal in 3 consecutive contests (190, 191, and 192), so we include him in the result table. - Note that he also won a medal in 3 other consecutive contests (194, 195, and 196). Alice won a medal in 3 consecutive contests (191, 192, and 193), so we include her in the result table. Quarz won a medal in 5 consecutive contests (190, 191, 192, 193, and 194), so we include them in the result table. ``` 来源：力扣（LeetCode） 链接： 著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。 ## Solution 1. t表列出所有选手的获奖情况，窗口函数做出连续获奖reference column 2. u表对两个条件筛出的user\_id取并集：金牌3次 union 连续获奖3次 ```sql with t as ( SELECT *, contest_id - RANK() over (PARTITION BY user_id,user_id in (gold_medal, silver_medal, bronze_medal) order by contest_id) consecutive_ref FROM Contests JOIN Users on user_id in (gold_medal, silver_medal, bronze_medal) order by user_id, contest_id ), u as ( SELECT user_id from t GROUP BY 1 having sum(user_id=gold_medal)>=3 union select user_id FROM t GROUP BY user_id,consecutive_ref HAVING count(1) >=3 ) SELECT name, mail FROM u LEFT JOIN Users using(user_id); ``` ## Table Schema ```sql Create table If Not Exists Contests (contest_id int, gold_medal int, silver_medal int, bronze_medal int); Create table If Not Exists Users (user_id int, mail varchar(50), name varchar(30)); Truncate table Contests; insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('190', '1', '5', '2'); insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('191', '2', '3', '5'); insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('192', '5', '2', '3'); insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('193', '1', '3', '5'); insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('194', '4', '5', '2'); insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('195', '4', '2', '1'); insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('196', '1', '5', '2'); Truncate table Users; insert into Users (user_id, mail, name) values ('1', 'sarah@leetcode.com', 'Sarah'); insert into Users (user_id, mail, name) values ('2', 'bob@leetcode.com', 'Bob'); insert into Users (user_id, mail, name) values ('3', 'alice@leetcode.com', 'Alice'); insert into Users (user_id, mail, name) values ('4', 'hercy@leetcode.com', 'Hercy'); insert into Users (user_id, mail, name) values ('5', 'quarz@leetcode.com', 'Quarz'); ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://zqt0.gitbook.io/leetcode/sql/1811.find-interview-candidates.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.