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
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/
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.