Amazon SQL interview Questions – Leetcode 584 – Find Customer Referee

Problem Description –

Given a table customer holding customers’ information and the referee.

Write a query to return the list of customers NOT referred by the person with id ‘2’.

For the sample data above, the result is:

Difficulty Level – Easy

Solution –

SELECT 
    name
FROM customer
WHERE id NOT IN (
                SELECT
                    id
                FROM customer
                WHERE referee_id = 2
                )

In the inner subquery, we are selecting all the customer id which is referred by the person with referee_id = 2, and then In the outer query, we are filtering out all the id’s from the inner subquery as we need to find all the customers id which is not referred by the person with id 2.