584. Find Customer Referee - Detailed Explanation

Problem Statement

Description:
You are given a table called Customers that contains customer information. The table has the following columns:

• customer_id: A unique identifier for each customer.

• customer_name: The name of the customer.

• referee_id: The ID of the referee who referred the customer. This value can be NULL if the customer was not referred by anyone. If not NULL, it will refer to another customer in the same table.

Your task is to write an SQL query to report the customer along with the name of their referee. If a customer does not have a referee, return NULL for the referee’s name.

Example 1:

Expected Output:

Example 2:

Expected Output:

Example 3:

Expected Output:

Constraints:

• Every non-NULL referee_id in the table references a valid customer_id in the same table.
• The table can contain any number of rows, so efficiency matters.

Hints Before You Start

• Hint 1: Think about how you can join the Customers table with itself. You need to “look up” the referee’s name for each customer.
• Hint 2: Use a LEFT JOIN so that customers who do not have a referee (i.e., where referee_id is NULL) are still included in the result, with a NULL value for the referee’s name.

Approaches

Using a Self-Join

Concept:

Since both the customer and their referee are stored in the same table, you can treat one instance of the table as the primary customer data and a second instance as the reference data for referees. Use table aliases to differentiate between these two roles.

Steps:

1. Alias the Table:
   • Use alias c for the main customer records.
   • Use alias r for the referee records.
2. Join Condition:
   • Join on c.referee_id = r.customer_id.
3. Use LEFT JOIN:
   • This ensures that if c.referee_id is NULL (or there is no matching referee), the customer still appears in the result with a NULL value for the referee's name.

Using a Subquery

Concept:

Another approach is to use a subquery to fetch the referee’s name. For each row in the Customers table, select the corresponding referee’s name from the same table using a subquery.

Steps:

1. For each customer, write a subquery to select the referee's name where the referee’s customer_id matches the customer’s referee_id.
2. This approach can be less efficient than a self-join for larger datasets.

SQL Code Implementation

Using a Self-Join

SELECT 
    c.customer_id, 
    c.customer_name, 
    r.customer_name AS referee_name
FROM 
    Customers c
LEFT JOIN 
    Customers r ON c.referee_id = r.customer_id;

Explanation:

• FROM Customers c:
  Treats the Customers table as the primary table (alias c).
• LEFT JOIN Customers r ON c.referee_id = r.customer_id:
  Joins the same table (alias r) using the condition that referee_id from the primary table matches customer_id from the referee table. A LEFT JOIN ensures that customers without a referee are included.
• SELECT ...:
  Selects the customer’s id, name, and the referee's name (if available).

Using a Subquery (Alternative Approach)

SELECT 
    customer_id, 
    customer_name,
    (
      SELECT customer_name 
      FROM Customers 
      WHERE customer_id = c.referee_id
    ) AS referee_name
FROM Customers c;

Explanation:

• For each row in the Customers table (aliased as c), a subquery is executed to retrieve the customer_name from the Customers table where the customer_id matches c.referee_id.
• If no matching row exists (i.e., when referee_id is NULL), the subquery returns NULL.