Find Customer Referee - Practice Coding Problems

Find Customer Referee - Problem

Database Easy

Imagine you're analyzing a customer database for a company's referral program. You have a table of customers where each customer might have been referred by another customer.

Goal: Find the names of customers who were NOT referred by customer with ID 2 specifically.

This includes:

Customers referred by any other customer (ID ≠ 2)
Customers who were not referred by anyone (NULL referee_id)

Customer Table Schema:

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| referee_id  | int     |
+-------------+---------+

The referee_id column contains the ID of the customer who made the referral, or NULL if no one referred them.

Input & Output

example_1.sql — Basic Example

$ Input: Customer table: +----+------+------------+ | id | name | referee_id | +----+------+------------+ | 1 | Will | null | | 2 | Jane | null | | 3 | Alex | 2 | | 4 | Bill | null | | 5 | Zack | 1 | | 6 | Mark | 2 | +----+------+------------+

💡 Note: Will, Jane, and Bill were not referred by anyone (null). Zack was referred by customer 1 (not 2). Alex and Mark were referred by customer 2, so they are excluded.

example_2.sql — All Null References

$ Input: Customer table: +----+------+------------+ | id | name | referee_id | +----+------+------------+ | 1 | John | null | | 2 | Mary | null | | 3 | Bob | null | +----+------+------------+

› Output: +------+ | name | +------+ | John | | Mary | | Bob | +------+

💡 Note: All customers have null referee_id, so none were referred by customer 2. All names are included in the result.

example_3.sql — Mixed References

$ Input: Customer table: +----+-------+------------+ | id | name | referee_id | +----+-------+------------+ | 1 | Alice | 3 | | 2 | Bob | 2 | | 3 | Carol | 1 | | 4 | Dave | 2 | +----+-------+------------+

💡 Note: Alice was referred by customer 3, Carol by customer 1. Bob and Dave were both referred by customer 2, so they are excluded.

Visualization

Tap to expand

Understanding the Visualization

Map the Network

Visualize all customers and their referral relationships

Identify Target

Mark customer ID 2 as the referrer to exclude

Filter Results

Select customers not referred by ID 2 or with no referrer

Key Takeaway

🎯 Key Insight: Use `WHERE referee_id != 2 OR referee_id IS NULL` to filter customers, remembering that NULL values require special handling in SQL comparisons.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single table scan through all customers

✓ Linear Growth

Space Complexity

O(1)

No additional space needed for filtering

✓ Linear Space

Constraints

1 ≤ id ≤ 1000
1 ≤ name.length ≤ 50
referee_id can be null or reference another customer's id
referee_id will not reference a non-existent customer

Asked in

a Amazon 25 ⊞ Microsoft 18 G Google 15 f Meta 12

This SQL problem requires filtering customers based on their referrer. The key insight is using WHERE referee_id != 2 OR referee_id IS NULL to handle both non-matching referees and customers without referees. The solution is O(n) time complexity with a single table scan.

Common Approaches

Approach	Time	Space	Notes
✓ Basic SQL Filtering	O(n)	O(1)	Use WHERE clause to exclude customers referred by ID 2

Basic SQL Filtering — Algorithm Steps

Select name column from Customer table
Apply WHERE condition for referee_id != 2
Handle NULL values using OR referee_id IS NULL
Return the filtered results

Visualization

Tap to expand

Step-by-Step Walkthrough

Initial Data

Start with all customers and their referee information

Apply Filter

Exclude customers with referee_id = 2

Result

Return names of remaining customers

Code -

solution.c — C

// SQL query simulation in C
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#define MAX_NAME_LEN 50
#define MAX_CUSTOMERS 100

typedef struct {
    int id;
    char name[MAX_NAME_LEN];
    int referee_id;
    int has_referee; // 0 for null, 1 for valid referee_id
} Customer;

void findCustomerReferee(Customer* customers, int count) {
    printf("Customers not referred by ID 2:\n");
    
    for (int i = 0; i < count; i++) {
        if (!customers[i].has_referee || customers[i].referee_id != 2) {
            printf("%s\n", customers[i].name);
        }
    }
}

int main() {
    Customer customers[] = {
        {1, "Will", 0, 0},     // no referee
        {2, "Jane", 0, 0},     // no referee
        {3, "Alex", 2, 1},     // referred by 2
        {4, "Bill", 0, 0},     // no referee
        {5, "Zack", 1, 1},     // referred by 1
        {6, "Mark", 2, 1}      // referred by 2
    };
    
    int count = sizeof(customers) / sizeof(customers[0]);
    findCustomerReferee(customers, count);
    
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single table scan through all customers

✓ Linear Growth

Space Complexity

O(1)

No additional space needed for filtering

✓ Linear Space

Constraints

1 ≤ id ≤ 1000
1 ≤ name.length ≤ 50
referee_id can be null or reference another customer's id
referee_id will not reference a non-existent customer

25.4K Views

Medium Frequency

~8 min Avg. Time

890 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Basic SQL Filtering — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler