Sales Analysis – 2

Tables – product and sales are provided. product table holds data of all products and sales table represent sale transaction that was completed.

Write SQL query to identify buyer_id who bought S10 but not iPhone.

---

Input Table: sales

seller_id	product_id	buyer_id	sale_date	quantity	price
11	10	71	2019-02-22	3	6000
11	20	72	2019-03-27	1	600
22	20	73	2019-07-04	1	900
33	30	74	2019-06-21	2	6600
22	10	74	2019-06-23	1	2000

 

---

Explanation:

• buyer_id = 71 is the only buyer who purchased only S10. 
• buyer_id = 74 bought both S10 and iPhone and that’s why it was excluded from final result.

---

DDLs:

CREATE TABLE IF NOT EXISTS product (
    product_id       INT,
    product_name VARCHAR(10),
    unit_price        INT
);
INSERT INTO product VALUES (10, 'S10', 2000);
INSERT INTO product VALUES (20, '7Pro', 600);
INSERT INTO product VALUES (30, 'iPhone', 3300);

CREATE TABLE IF NOT EXISTS sales (
    seller_id     INT,
    product_id INT,
    buyer_id    INT,
    sale_date  DATE,
    quantity     INT,
    price          INT
);
INSERT INTO sales VALUES (11, 10, 71, '2019-02-22', 3, 6000);
INSERT INTO sales VALUES (11, 20, 72, '2019-03-27', 1, 600);
INSERT INTO sales VALUES (22, 20, 73, '2019-07-04', 1, 900);
INSERT INTO sales VALUES (33, 30, 74, '2019-06-21', 2, 6600);
INSERT INTO sales VALUES (22, 10, 74, '2019-06-23', 1, 2000);

---

Solution:

SELECT
A.buyer_id
FROM
sales A
JOIN
product B
ON  A.product_id   = B.product_id
AND B.product_name IN ( "S10", "iPhone")
GROUP BY
A.buyer_id
HAVING SUM(CASE WHEN B.product_name="S10" THEN 1 ELSE 0 END)>0
    AND
    SUM(CASE WHEN B.product_name="iPhone" THEN 1 ELSE 0 END)=0
;