Find Interview Candidates

Find Interview Candidates

What is this problem about?

The Find Interview Candidates coding problem is a SQL task. You are typically given a Contests table (with contest IDs and winner IDs) and a Users table. Your goal is to identify users who are candidates for an interview based on two criteria:

1. They won three or more consecutive contests.
2. They won three or more distinct contests overall. The result should be a list of user names and emails.

Why is this asked in interviews?

Amazon uses this to test proficiency in SQL Window Functions and complex joins. it's a "real-world" reporting task that evaluates how you handle sequences and aggregations. Specifically, identifying "consecutive" records in a relational database is a high-level skill that demonstrates you can think about data topologically rather than just as a flat set.

Algorithmic pattern used

This follows the Database and Consecutive Sequence pattern.

1. For Criterion 1 (Consecutive Wins): Use ROW_NUMBER() or LAG()/LEAD(). A common trick is to calculate contest_id - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY contest_id). For a consecutive streak, this difference remains constant.
2. For Criterion 2 (Total Wins): A simple GROUP BY user_id with HAVING COUNT(*) >= 3.
3. Final Join: Use a UNION or OR to combine both sets of IDs and join with the Users table to get the final names and emails.

Example explanation

• User 1 wins contests 101, 102, 103.
  • contest_id - row_num: 101-1=100, 102-2=100, 103-3=100.
  • Since the diff is constant for 3 rows, User 1 is a candidate.
• User 2 wins contests 101, 105, 110.
  • Total wins = 3. User 2 is a candidate.

Common mistakes candidates make

• Ignoring the "Three or More" rule: Returning only users who won exactly three contests.
• Inefficient Subqueries: Using complex cross-joins to find consecutive IDs instead of window functions.
• Handling ties: Not correctly handling situations where multiple contests have the same winner (though usually contest IDs are unique).

Interview preparation tip

Master the "Gap and Island" problem in SQL. It is the standard template for finding consecutive ranges or streaks in time-series data.