Write a query to list all customers who have not placed any order in the last 90 days.

Answer

Use a LEFT JOIN (or NOT EXISTS) to find customers with no matching order in the last 90 days.

Solution

SELECT c.customer_id, c.name
FROM Customers c
LEFT JOIN Orders o
  ON o.customer_id = c.customer_id AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
WHERE o.order_id IS NULL;

Step by Step

1. The join condition includes both the customer match and the 90-day date filter - deliberately placed in the ON clause, not WHERE.
2. For customers with a recent order, the join succeeds and o.order_id is populated.
3. For customers with no order in that window, the left join produces a row with all Orders columns as NULL.
4. WHERE o.order_id IS NULL keeps exactly those customers - the ones with no matching recent order.

Alternative Method: NOT EXISTS

SELECT c.customer_id, c.name
FROM Customers c
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o
  WHERE o.customer_id = c.customer_id AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
);

NOT EXISTS often communicates intent more clearly ("no such order exists") and can perform better than a LEFT JOIN on some engines, since it can stop searching the instant one matching row is found.

Key Exam Traps

• Date filter placement is the critical trap: Putting the 90-day condition in WHERE instead of the join's ON clause silently turns the LEFT JOIN into an inner join - because WHERE filters out the NULL rows a left join is specifically meant to preserve. This is the single most common mistake in this exact pattern.
• Customers who never ordered at all: They also correctly appear in the result, since they have zero matching order rows - worth confirming whether "no order in 90 days" is meant to include never-ordered customers or only previously-active ones.