大家好，我是老吳，大家也可以叫我吳同學，再小一點的朋友也可以叫我吳師兄，歡迎大家跟我一起走進資料分析的世界，一起學習！

感興趣的朋友可以關注我的資料分析專欄，里面有許多優質的文章跟大家分享哦，
另外也歡迎大家關注我的SQL刷題專欄，里面有我分享的高質量SQL題，以及詳細的決議，

本篇博文又是我的SQL題分享環節，為了記錄一下自己的做題程序，以及防止后續力扣會員過期無法再次查看題目，所以我每天都會給大家選出一道質量較高的SQL題目發表到博客上，并且會加上自己的一些解題技巧或實用知識點，希望對大家也能有所幫助，

那么今天給大家分享的題是LeetCode的第811題——尋找面試候選人，

下面是問題的詳細描述，

問題描述

表: Contests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| contest_id   | int  |
| gold_medal   | int  |
| silver_medal | int  |
| bronze_medal | int  |
+--------------+------+
contest_id 是該表的主鍵.
該表包含LeetCode競賽的ID和該場比賽中金牌、銀牌、銅牌的用戶id，
可以保證，所有連續的比賽都有連續的ID，沒有ID被跳過，

Table: Users

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| mail        | varchar |
| name        | varchar |
+-------------+---------+
user_id 是該表的主鍵.
該表包含用戶資訊，

撰寫 SQL 陳述句來回傳面試候選人的 姓名和 郵件.當用戶滿足以下兩個要求中的任意一條，其成為面試候選人:

• 該用戶在連續三場及更多比賽中贏得獎牌，
• 該用戶在三場及更多不同的比賽中贏得金牌（這些比賽可以不是連續的）
  可以以任何順序回傳結果，

查詢結果格式如下例所示：

Contests表:
+------------+------------+--------------+--------------+
| 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表:
+---------+--------------------+-------+
| 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 |
+---------+--------------------+-------+

結果表:
+-------+--------------------+
| name  | mail               |
+-------+--------------------+
| Sarah | sarah@leetcode.com |
| Bob   | bob@leetcode.com   |
| Alice | alice@leetcode.com |
| Quarz | quarz@leetcode.com |
+-------+--------------------+

Sarah 贏得了3塊金牌 (190, 193, and 196),所以我們將她列入結果表，
Bob在連續3場競賽中贏得了獎牌(190, 191, and 192), 所以我們將他列入結果表，
    - 注意他在另外的連續3場競賽中也贏得了獎牌(194, 195, and 196).
Alice在連續3場競賽中贏得了獎牌 (191, 192, and 193), 所以我們將她列入結果表，
Quarz在連續5場競賽中贏得了獎牌(190, 191, 192, 193, and 194), 所以我們將他列入結果表，

解題思路

1. 第二個條件很容易實作：用roup by gold_medal即可篩選出次數大于等于3的，然后left join一下users表即可得到name和mail，
2. 關鍵是第一個條件，怎么才能連續三場都拿獎牌的人呢？
3. 這里分享一個這種型別題的小技巧：用row_number函式，標好每行的序號，然后contest_id減去序號，差相等即代表連續，
4. 記住這個小技巧，后面要用到，
5. 首先理清我們的目標：查看每一個用戶的考試情況，然后看看他有沒有連續三次拿獎的情況，
6. 怎么查看呢？這里就需要用到表連接，連接的條件是user_id出現在了金銀銅中即可，
7. 連接完之后呢，用視窗函式，對每個用戶磁區，并按contest_id排序，排好序之后，用contest_id-序號，得到差值，
8. 最后根據用戶id和這個差值分組，篩選出組大小不小于3的即可，
9. 最終union第二個條件得到的資料即可，

代碼實作

select name, mail
from (
    select name, mail,
        contest_id - row_number() over(partition by user_id order by contest_id) rn,
        user_id 
    from Contests
    join Users
    on user_id in (gold_medal, silver_medal, bronze_medal) 
) as tmp
group by user_id, rn
having count(1) >= 3
union
select name, mail
from Contests
join Users
on gold_medal = user_id
group by gold_medal
having count(1) >= 3

結束語

看完這篇，還有更多知識點分享給你哦，自己慢慢找哈，就在下面鏈接，

推薦關注的專欄

👨?👩?👦?👦 機器學習：分享機器學習實戰專案和常用模型講解
👨?👩?👦?👦 資料分析：分享資料分析實戰專案和常用技能整理

往期內容回顧

💚 學習Python全套代碼【超詳細】Python入門、核心語法、資料結構、Python進階【致那個想學好Python的你】
?? 學習pandas全套代碼【超詳細】資料查看、輸入輸出、選取、集成、清洗、轉換、重塑、數學和統計方法、排序
💙 學習pandas全套代碼【超詳細】分箱操作、分組聚合、時間序列、資料可視化
💜 學習NumPy全套代碼【超詳細】基本操作、資料型別、陣列運算、復制和試圖、索引、切片和迭代、形狀操作、通用函式、線性代數

CSDN@報告，今天也有好好學習