Problem Description

In this problem, we have two SQL tables: Contests and Users.

The Contests table has the following columns:

• contest_id (integer): the primary key for this table, representing the contest ID.
• gold_medal (integer): the user ID of the gold medalist.
• silver_medal (integer): the user ID of the silver medalist.
• bronze_medal (integer): the user ID of the bronze medalist.

The Users table has the following columns:

• user_id (integer): the primary key for this table, representing the user ID.
• mail (varchar): the email address of the user.
• name (varchar): the name of the user.

The problem requires us to write an SQL query to report the name and the mail of all interview candidates. A user is considered an interview candidate if at least one of these two conditions is true:

1. The user won any medal in three or more consecutive contests.
2. The user won the gold medal in three or more different contests (not necessarily consecutive).

Example

Let's walk through the example given in the problem description to understand the requirements clearly:

Contests table:

+------------+------------+--------------+--------------+
| 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 table:

+---------+--------------------+-------+
| user_id | mail               | name  |
+---------+--------------------+-------+
| 1       | [email protected] | Sarah |
| 2       | [email protected]   | Bob   |
| 3       | [email protected] | Alice |
| 4       | [email protected] | Hercy |
| 5       | [email protected] | Quarz |
+---------+--------------------+-------+

Result table:

+-------+--------------------+
| name  | mail               |
+-------+--------------------+
| Sarah | [email protected] |
| Bob   | [email protected]   |
| Alice | [email protected] |
| Quarz | [email protected] |
+-------+--------------------+

Explanation:

• Sarah won 3 gold medals (190, 193, and 196), so we include her in the result table.
• Bob won a medal in 3 consecutive contests (190, 191, and 192), so we include him in the result table.
  • Note that he also won a medal in 3 other consecutive contests (194, 195, and 196).
• Alice won a medal in 3 consecutive contests (191, 192, and 193), so we include her in the result table.
• Quarz won a medal in 5 consecutive contests (190, 191, 192, 193, and 194), so we include them in the result table.

Approach

To solve this problem, we can combine the information from both tables and use the aggregation feature of SQL to count the number of consecutive contest wins and the number of gold medals won by each user.

1. First, we will create a temporary table that stores all the medals won by each user, along with their contest IDs.
2. In the next step, we will count the number of gold medals won by each user and join the results with the Users table.
3. We will then add a column with the number of consecutive contest wins for each user by counting their consecutive contests.
4. Finally, we will select only users who meet at least one of the two conditions and return their name and email.

SQL Query

Here's the SQL query that implements the approach described above:

WITH medal_counts AS (
    SELECT user_id, contest_id, contest_id - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY contest_id) AS group_id
    FROM (
        SELECT gold_medal AS user_id, contest_id FROM Contests
        UNION ALL
        SELECT silver_medal AS user_id, contest_id FROM Contests
        UNION ALL
        SELECT bronze_medal AS user_id, contest_id FROM Contests
    ) medals
)
, gold_medals AS (
    SELECT user_id, COUNT(*) AS gold_medal_count
    FROM Contests
    GROUP BY user_id
)
SELECT u.name, u.mail
FROM Users u
JOIN gold_medals gm ON u.user_id = gm.user_id
WHERE gm.gold_medal_count >= 3
OR EXISTS (
    SELECT 1
    FROM medal_counts
    WHERE user_id = u.user_id
    GROUP BY user_id, group_id
    HAVING COUNT(*) >= 3
)

The query first creates a temporary table with all medals won by each user and another temporary table with the count of gold medals won by each user. Then it selects users who have won 3 or more gold medals or have won any medals in 3 or more consecutive contests.## Alternative Approach

Another approach is to first find all users who won a medal in three or more consecutive contests, and then find all users who won the gold medal in three or more different contests. Finally, we can union these results and join them with the Users table to obtain their name and email.

SQL Query

Here's the SQL query that implements the alternative approach:

WITH medals AS (
    SELECT DISTINCT user_id, contest_id
    FROM (
        SELECT gold_medal AS user_id, contest_id FROM Contests
        UNION ALL
        SELECT silver_medal AS user_id, contest_id FROM Contests
        UNION ALL
        SELECT bronze_medal AS user_id, contest_id FROM Contests
    ) medals
)
, consecutive_medals AS (
    SELECT user_id
    FROM (
        SELECT user_id, contest_id, contest_id - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY contest_id) AS group_id
        FROM medals
    ) medal_group
    GROUP BY user_id, group_id
    HAVING COUNT(*) >= 3
)
, gold_medalists AS (
    SELECT user_id
    FROM Contests
    GROUP BY user_id
    HAVING COUNT(gold_medal) >= 3
)

SELECT u.name, u.mail
FROM Users u
WHERE EXISTS (SELECT 1 FROM consecutive_medals WHERE user_id = u.user_id)
OR EXISTS (SELECT 1 FROM gold_medalists WHERE user_id = u.user_id)

This query first creates a temporary table with all unique medal winning user_ids and contest_ids. It then finds users who won a medal in three or more consecutive contests and users who won the gold medal in three or more different contests. Finally, it selects users that meet either of the conditions and returns their name and email.