Accelerated Hiring Shortlist from Contest Medal History

MEDIUM20 pts

A talent-operations team wants to identify high-signal competitors from historical programming contest podium records.

Table: contests

contest_id: unique contest identifier
gold_medal: user_id of the gold winner
silver_medal: user_id of the silver winner
bronze_medal: user_id of the bronze winner

Table: users

user_id: unique user identifier
mail: contact email
name: candidate display name

Data guarantees:

contest_id is unique.
Contest IDs are consecutive with no missing values between minimum and maximum contest_id.

A user should be included in the accelerated-hiring shortlist if at least one condition is true:

The user won any medal in 3 or more consecutive contests.
The user won the gold medal in 3 or more different contests.

Task: Return shortlisted users.

Output requirements:

Return exactly these columns in this order:
1. name
2. mail
Sort by name in ascending order.
For ties on name, sort by mail in ascending order.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

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       | aline@example.com    | Aline |
| 2       | bora@example.com     | Bora  |
| 3       | cato@example.com     | Cato  |
| 4       | dina@example.com     | Dina  |
| 5       | ezra@example.com     | Ezra  |

Output

[
  {"name":"Aline","mail":"aline@example.com"},
  {"name":"Bora","mail":"bora@example.com"},
  {"name":"Cato","mail":"cato@example.com"},
  {"name":"Ezra","mail":"ezra@example.com"}
]

Explanation

Aline has at least three gold medals. Bora and Cato each have a streak of three consecutive contests with at least one medal. Ezra has a longer consecutive-medal streak.

Example

Input

contests:
| contest_id | gold_medal | silver_medal | bronze_medal |
|------------|------------|--------------|--------------|
| 410        | 10         | 11           | 12           |
| 411        | 13         | 11           | 14           |
| 412        | 15         | 11           | 16           |
| 413        | 10         | 14           | 12           |
| 414        | 17         | 18           | 19           |
| 415        | 20         | 21           | 22           |
| 416        | 10         | 23           | 24           |

users:
| user_id | mail                 | name   |
|---------|----------------------|--------|
| 10      | nova@example.com     | Nova   |
| 11      | rhea@example.com     | Rhea   |
| 12      | sol@example.com      | Sol    |
| 13      | tala@example.com     | Tala   |
| 14      | uma@example.com      | Uma    |
| 15      | vale@example.com     | Vale   |
| 16      | wyatt@example.com    | Wyatt  |
| 17      | xara@example.com     | Xara   |
| 18      | yuri@example.com     | Yuri   |
| 19      | zane@example.com     | Zane   |
| 20      | amos@example.com     | Amos   |
| 21      | brix@example.com     | Brix   |
| 22      | cleo@example.com     | Cleo   |
| 23      | dion@example.com     | Dion   |
| 24      | erin@example.com     | Erin   |

Output

[
  {"name":"Nova","mail":"nova@example.com"},
  {"name":"Rhea","mail":"rhea@example.com"}
]

Explanation

Nova qualifies by three non-consecutive gold medals (410, 413, 416). Rhea qualifies by appearing in contests 410, 411, and 412 consecutively.

Example

Input

contests:
| contest_id | gold_medal | silver_medal | bronze_medal |
|------------|------------|--------------|--------------|
| 900        | 31         | 32           | 33           |
| 901        | 34         | 35           | 36           |

users:
| user_id | mail               | name  |
|---------|--------------------|-------|
| 31      | a31@example.com    | A31   |
| 32      | a32@example.com    | A32   |
| 33      | a33@example.com    | A33   |
| 34      | a34@example.com    | A34   |
| 35      | a35@example.com    | A35   |
| 36      | a36@example.com    | A36   |

Output

[]

Explanation

No user can satisfy either condition when only two contests exist.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in users <= 300000
0 <= number of rows in contests <= 300000
1 <= user_id <= 1000000000
1 <= contest_id <= 1000000000
users.user_id is unique
users.mail is unique
contests.contest_id is unique
Contest IDs are consecutive with no gaps
gold_medal, silver_medal, bronze_medal each reference a valid users.user_id
Within one contest row, medal user IDs are pairwise distinct
A user qualifies with any-medal streak length >= 3 or gold count >= 3
Return exactly columns: name, mail
Sort by name ASC, then mail ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exact columns: name, mail

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

users =

[{"user_id":1,"mail":"aline@example.com","name":"Aline"},{"user_id":2,"mail":"bora@example.com","name":"Bora"},{"user_id":3,"mail":"cato@example.com","name":"Cato"},{"user_id":4,"mail":"dina@example.com","name":"Dina"},{"user_id":5,"mail":"ezra@example.com","name":"Ezra"}]

contests =

[{"contest_id":190,"gold_medal":1,"silver_medal":5,"bronze_medal":2},{"contest_id":191,"gold_medal":2,"silver_medal":3,"bronze_medal":5},{"contest_id":192,"gold_medal":5,"silver_medal":2,"bronze_medal":3},{"contest_id":193,"gold_medal":1,"silver_medal":3,"bronze_medal":5},{"contest_id":194,"gold_medal":4,"silver_medal":5,"bronze_medal":2},{"contest_id":195,"gold_medal":4,"silver_medal":2,"bronze_medal":1},{"contest_id":196,"gold_medal":1,"silver_medal":5,"bronze_medal":2}]

Accelerated Hiring Shortlist from Contest Medal History

Hints

Accelerated Hiring Shortlist from Contest Medal History

Hints