Find Customer Referee - Problem

Given a Customer table with customer information and referral relationships, find the names of customers who are either:

  • Not referred by customer with id = 2 (referred by any other customer)
  • Not referred by any customer (referee_id is NULL)

Return the result table in any order.

Table Schema

Customer
Column Name Type Description
id PK int Primary key - unique customer identifier
name varchar Customer name
referee_id int ID of the customer who referred this customer (can be NULL)
Primary Key: id
Note: Each row represents a customer and their referrer. referee_id can be NULL if no referrer exists.

Input & Output

Example 1 — Basic Filtering
Input Table:
id name referee_id
1 Will 2
2 Jane
3 Alex 1
4 Bill
5 Zack 1
Output:
name
Jane
Alex
Bill
Zack
💡 Note:

Will is excluded because he was referred by customer with id = 2. Jane and Bill are included because they have no referrer (NULL). Alex and Zack are included because they were referred by customer with id = 1 (not 2).
Example 2 — All NULL Referees
Input Table:
id name referee_id
1 Alice
2 Bob
Output:
name
Alice
Bob
💡 Note:

Both customers have no referrer (referee_id is NULL), so both are included in the result since they were not referred by customer with id = 2.

Constraints

  • 1 ≤ Customer.id ≤ 1000
  • Customer.name consists of uppercase and lowercase letters
  • referee_id can be NULL or reference another customer's id

Visualization

Tap to expand
Find Customer Referee INPUT Customer Table id name referee_id 1 Will NULL 2 Jane 2 3 Alex 2 4 Bill NULL 5 Zack 1 6 Mark 2 = Referred by id=2 Find customers where: referee_id != 2 OR referee_id IS NULL ALGORITHM STEPS 1 Scan Table Read each customer row 2 Check NULL Is referee_id NULL? 3 Check Value Is referee_id != 2? 4 Return Names Output matching names SELECT name FROM Customer WHERE referee_id != 2 OR referee_id IS NULL; Alternative: IFNULL(referee_id, 0) != 2 FINAL RESULT name Will Bill Zack Customers NOT referred by id=2: Will - referee_id is NULL Bill - referee_id is NULL Zack - referee_id is 1 EXCLUDED (referee_id = 2): Jane, Alex, Mark 3 rows returned Key Insight: NULL values require special handling in SQL. The condition "referee_id != 2" does NOT match NULL values because NULL represents unknown/missing data. You must explicitly check "OR referee_id IS NULL" or use IFNULL/COALESCE functions to handle NULLs. This is a common SQL pitfall in interview questions. TutorialsPoint - Find Customer Referee | Optimal Solution

Related Problems

Asked in
Amazon 23 Facebook 18
28.5K Views
High Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen