Find Customer Referee

You are tasked with identifying customers who were referred by a specific referee. This is a common requirement in marketing and sales to track referral programs and reward referrers. The goal is to extract a list of all customers whose referee_id matches a given target referee ID.

Problem Description

Given a table named Customer with the following columns:

• id (integer): The unique identifier for each customer.
• name (string): The name of the customer.
• referee_id (integer or NULL): The ID of the customer who referred this customer. If a customer was not referred, this value is NULL.

You need to write a function or query that returns the id and name of all customers where the referee_id is exactly 2. Customers who were not referred (i.e., referee_id is NULL) or referred by someone else should be excluded.

Key Requirements:

• Select customers based on a specific referee_id.
• The referee_id to match is 2.
• Exclude customers where referee_id is NULL.

Expected Behavior: The output should be a list of customers that meet the criteria, containing their id and name. The order of the output does not matter.

Edge Cases:

• What if there are no customers referred by ID 2? The output should be an empty list.
• What if the Customer table is empty? The output should be an empty list.

Examples

Example 1:

Input:
Customer Table:
| id  | name    | referee_id |
|-----|---------|------------|
| 1   | John    | NULL       |
| 2   | Jane    | NULL       |
| 3   | Alex    | 2          |
| 4   | Bob     | 1          |
| 5   | Charlie | 2          |

Output:

| id  | name    |
|-----|---------|
| 3   | Alex    |
| 5   | Charlie |

Explanation: Customers with id 3 and 5 have referee_id equal to 2.

Example 2:

Input:
Customer Table:
| id  | name    | referee_id |
|-----|---------|------------|
| 10  | Alice   | 5          |
| 20  | Bob     | 10         |
| 30  | Carol   | 15         |

Output:

| id  | name    |
|-----|---------|

Explanation: No customers have referee_id equal to 2.

Example 3:

Input:
Customer Table:
| id  | name    | referee_id |
|-----|---------|------------|
| 1   | A       | 3          |
| 2   | B       | 3          |
| 3   | C       | NULL       |
| 4   | D       | 2          |
| 5   | E       | 3          |

Output:

| id  | name    |
|-----|---------|
| 4   | D       |

Explanation: Only customer with id 4 has referee_id equal to 2. Customers with referee_id 3 or NULL are excluded.

Constraints

• The Customer table can contain between 0 and 1000 rows.
• id is a unique integer from 1 to 1000.
• name is a string, not exceeding 50 characters.
• referee_id is either an integer from 1 to 1000 or NULL.
• The function/query should be efficient enough to handle up to 1000 rows.

Notes

• Think about how to filter rows based on a specific column value.
• Consider how to handle missing or null values in the referee_id column.
• Pseudocode is acceptable for describing the logic.