SQL Interview Questions – Sales Analysis II

Problem Description –

Write an SQL query that reports the buyers who have bought S8 but not iPhone. Note that S8 and iPhone are products present in the Product table.

Return the result table in any order.

The query result format is in the following example.

Difficulty Level – Easy

Problem Link – https://leetcode.com/problems/sales-analysis-ii/

Solution –

WITH t1 AS (
SELECT
    s.buyer_id,
    s.product_id,
    p.product_name
FROM sales as s JOIN product as p
ON s.product_id = p.product_id
)
SELECT
    DISTINCT buyer_id
FROM sales
WHERE buyer_id IN (SELECT buyer_id FROM t1 WHERE product_name = 'S8')
AND buyer_id NOT IN (SELECT buyer_id FROM t1 WHERE product_name = 'iPhone') 

This is another kind of common SQL interview questions where you are asked to find something which satisfy some condition but not the other condition. Here we are asked to reports the buyers who have bought S8 but not iPhone.

The trick to solve these kind of problems is to first select all the id which satisfy a particular condition that you were asked to ignore in this case iPhone then in the outer query use NOT IN to filter those ids.