Leetcode Problem 1811. Find Interview Candidates

1811. Find Interview Candidates

Leetcode Solutions

MySQL window function solution

Use UNION ALL to combine gold_medal, silver_medal, and bronze_medal into a single column named user along with their corresponding contest_id.
Apply the ROW_NUMBER() window function partitioned by user and ordered by contest_id to assign a rank to each medal.
Calculate the difference between contest_id and the rank to identify consecutive medals.
Group by user and the calculated difference to find users with three or more consecutive medals.
Separately, group by gold_medal to find users with three or more gold medals.
Use UNION ALL to combine the results from steps 4 and 5.
Join the result with the Users table to get the name and mail of the interview candidates.
Use DISTINCT to remove any duplicates resulting from the union operation.

erDiagram
    Contests {
        int contest_id PK
        int gold_medal
        int silver_medal
        int bronze_medal
    }
    Users {
        int user_id PK
        varchar mail
        varchar name
    }
    Contests ||--o{ Users : "contains"

MySQL self-join and aggregation solution

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 1811. Find Interview Candidates

Leetcode Solutions

MySQL window function solution

Solution Idea

Query Steps

Code Implementation

MySQL self-join and aggregation solution

Ask Question

Suggested Answer