Start learning free

LESSON 40 OF 66 · SQL: INTERVIEW QUESTIONS

Bought One but Not the Other in Python

Practice

An interviewer asks: "Find the customers who bought the Mug but never the Lamp." A WHERE on the joined rows can't answer it: it sees one row at a time, and the Lamp sits in some other row, maybe in another order. You need a test about all of a customer's rows.

EXISTS (subquery) is true when the subquery finds at least one row, and NOT EXISTS when it finds none. The subquery can use the outer query's row. For example, the products nobody has ordered yet:

SELECT name
FROM products
WHERE NOT EXISTS (SELECT 1 FROM order_items WHERE product_id = products.id)

A second way works on whole results: A EXCEPT B keeps the rows of A that B doesn't return, each only once. The bought CTE is given: one row per product line, with customer_id, name and product_id. The Mug is product 1 and the Lamp is product 3. The check adds a June order with a Mug for Dan Weber, who also bought the Lamp. Both queries must print:

name
Ana Pop
Chloe Ng
(2 rows)
name
Ana Pop
Chloe Ng
(2 rows)

Your task: finish query with an EXISTS for the Mug and a NOT EXISTS for the Lamp, then finish except_query with EXCEPT and the Lamp select.


Assignment
Follow the Coding Tutorial to finish both queries, one clause per line.


Hint
Both subqueries read bought and match customer_id = customers.id, so each customer is checked against their own rows; only the product id changes.


STEP 1 OF 4
    Ctrl + Enter to check

    Sqlq Bought A Not B in Python

    Keep going with Python1,600+ step-by-step lessons, from your first line of code to coding interviews. Free to start.
    Start learning free