Table: Customer
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | referee_id | int | +-------------+---------+ In SQL, id is the primary key column for this table. Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.
Find the names of the customer that are not referred by the customer with id = 2.
Return the result table in any order.
The result format is in the following example.
Example 1:
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 | +----+------+------------+ Output: +------+ | name | +------+ | Will | | Jane | | Bill | | Zack | +------+
This approach involves using a simple SQL query with a WHERE clause to filter out customers who are referred by the customer with id = 2. We need to select all entries where referee_id is not equal to 2, including those where referee_id is NULL.
The SQL query selects the 'name' from the 'Customer' table where the 'referee_id' is either NULL or not equal to 2. By using 'referee_id IS NULL' in the WHERE clause, we also include customers who were not referred by anyone.
Time Complexity: O(n), where n is the number of rows in the Customer table.
Space Complexity: O(1), since the query operates in-place within the database.
This method utilizes the NOT IN clause to exclude specific entries from the result. By listing all customer ids that have referee_id of 2 and filtering them out, we can achieve the desired result.
This SQL query achieves the same goal through the use of a subquery. The subquery selects all customer ids with a 'referee_id' of 2. The outer query then selects the names of customers whose ids are not in that subquery result.
Time Complexity: O(n), where n is the number of rows in the Customer table.
Space Complexity: O(1), as the query executes using the database's inbuilt mechanisms.
This approach involves filtering the list of customers based on the referee_id. The goal is to select customers whose referee_id is either NULL or not equal to 2. This solution exploits the fact that SQL can be used to construct a SELECT statement with a conditional WHERE clause to filter out the desired rows.
This SQL query selects the name of customers where the referee_id is either NULL or not equal to 2. The 'IS NULL' condition ensures that customers without any referrer are included. The '!= 2' condition ensures that customers referred by customer 2 are excluded.
Time Complexity: O(n), where 'n' is the total number of rows in the table, because we need to scan each row.
Space Complexity: O(n), where 'n' is the maximum possible number of results stored in the output.
This approach uses a subquery to identify and exclude customers who were referred by the customer with id = 2. By constructing a subquery, the main query is able to filter the rest of the customers from the full list who do not appear in the subquery's result set.
The main query selects all name entries from the Customer table where the id is not in the list returned by the subquery. The subquery retrieves the id of customers with a referee_id of 2, effectively excluding these entries from the result set.
Time Complexity: O(n^2), since each candidate row is compared against the subquery results.
Space Complexity: O(1) if the subquery results can be stored efficiently in temporary space with respect to the total number of customers.
| Approach | Complexity |
|---|---|
| Approach 1: SQL WHERE Clause | Time Complexity: O(n), where n is the number of rows in the Customer table. |
| Approach 2: SQL NOT IN Clause | Time Complexity: O(n), where n is the number of rows in the Customer table. |
| Filtering Customers by Referee ID | Time Complexity: O(n), where 'n' is the total number of rows in the table, because we need to scan each row. |
| Subquery to Exclude Referrals | Time Complexity: O(n^2), since each candidate row is compared against the subquery results. |
2. Find Customer Referee Leetcode | SQL Tutorial for Beginners • Start Practicing • 10,625 views views
Watch 9 more video solutions →Practice Find Customer Referee with our built-in code editor and test cases.
Practice on FleetCode