Find Customer Referee - Leetcode Solution

Problem statement:

You are given a table named customer_referee, that contains 2 columns: customer_id and referee_id, where customer_id is the id of a customer and referee_id is the id of the customer’s referee.

The customer with id = 0 doesn’t have a referee, hence you don’t need to do anything with that customer_id.

Your task is to print the customer_id and the referee_id of their referee in a single row separated by a space. If a customer doesn’t have a referee, print -1 instead.

Solution:

We can start by using a left join on the table itself, joining on the referee_id column. This will give us all the records where a customer has a referee. However, we also need to include the customers who do not have a referee, hence we must use a left join instead of an inner join.

After the join, we can select the columns we need and use IFNULL() function to replace the referee_id with -1 if it is null (i.e. if the customer does not have a referee).

The SQL query for the solution is as follows:

SELECT
  c.customer_id,
  IFNULL(cr.referee_id, -1) AS referee_id
FROM
  customer c
  LEFT JOIN customer_referee cr ON c.customer_id = cr.customer_id
WHERE
  c.customer_id != 0;

Explanation:

• We select the columns customer_id and referee_id and use IFNULL() to set the value of referee_id to -1 if it is null.
• We join the customer table with the customer_referee table on the customer_id column using a left join.
• We use a WHERE clause to exclude the customer with id = 0, as per the problem statement.

The output of this query will be a table with two columns – customer_id and referee_id – containing the corresponding data as per the problem statement.

This solution has a time complexity of O(n), where n is the number of records in the customer table, as we need to perform a left join on the table. However, the use of indexes on the customer_id column can potentially improve the performance of this operation.

1