Accelerated Hiring Shortlist from Contest Medal History

Medium

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: name 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 1

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 2

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 3

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.

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

Loading problem...

Preparing your editor. You can read the problem while it loads.

Accelerated Hiring Shortlist from Contest Medal History

Medium

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: name 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 1

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 2

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 3

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.

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