Wished, but Not Bought

Now the wishlist starts to earn money. Next week, the shop wants to send a discount offer to everyone who has a game on their wishlist but hasn't bought it: “A game from your wishlist is 20% off!” Some customers have bought a game they wished for since then, and an offer for it would only annoy them.

What does “hasn't bought it” mean exactly? That there's no paid order of this customer with this game in it. A refunded order doesn't count: Jack Harris bought Shadow Ninja but returned it, so he can still get an offer. And an offer makes sense only for games that are out: nobody can buy Robot Rebellion or Mystery Manor yet.

“There's no row such that…” is a job for NOT EXISTS. For each row of the outer query, the database runs the subquery, and NOT EXISTS keeps the row when the subquery finds nothing. The subquery uses columns of the outer row, so it's a correlated subquery. For example, the customers who haven't written a single review:

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT *
    FROM reviews AS r
    WHERE r.customer_id = c.id
)
ORDER BY c.name;

In your query, the subquery looks for an order of the same customer (o.customer_id = w.customer_id), with the same game (i.game_id = w.game_id), that is paid. The customer and the status are in orders, the game is in order_items, so the subquery joins those two tables.

Typical mistake: leaving out one of the conditions that tie the subquery to the outer row. Without o.customer_id = w.customer_id, the subquery asks whether anyone at all has bought the game, and Liam Turner gets no offer for Knight's Oath just because Emma Clark has bought it.