BBampaa

Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Which customers have gone quiet?

As of 31 March 2026, which customers haven't ordered in the last 30 days? Gone quiet is about each customer's last order, not any order: work out MAX(order_date) for each customer, then keep the ones before the cut-off. A rule about a total, so HAVING.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Each customer's last order, against a date

SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id
HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '30 days'

Result:

customer_idlast_order
C1062026-02-22

Step by step

  1. Only the ones with no recent order.
    SELECT customer_id, MAX(order_date) AS last_order FROM orders GROUP BY customer_id HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '30 days'
  2. No join.
    SELECT customer_id, MAX(order_date) AS last_order FROM orders GROUP BY customer_id HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '30 days'
  3. HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '30 days'.
    SELECT customer_id, MAX(order_date) AS last_order FROM orders GROUP BY customer_id HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '30 days'
  4. MAX(order_date) with GROUP BY customer_id.
    SELECT customer_id, MAX(order_date) AS last_order FROM orders GROUP BY customer_id HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '30 days'

Practice

Try each one in your head first, then open the answer.

1. As of 31 March 2026, which customers haven't ordered in the last 30 days? Show customer_id and their last_order.
SELECT customer_id, MAX(order_date) AS last_order FROM orders GROUP BY customer_id HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '30 days'
2. As of 31 March 2026, show each customer_id and how many days since their last order, longest first. Name it days_since.
SELECT customer_id, DATE '2026-03-31' - MAX(order_date) AS days_since FROM orders GROUP BY customer_id ORDER BY days_since DESC
3. As of 31 March 2026, which customers haven't ordered in the last 14 days? Show customer_id and last_order.
SELECT customer_id, MAX(order_date) AS last_order FROM orders GROUP BY customer_id HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '14 days'
4. As of 31 March 2026, which stores haven't had a sale in the last 7 days? Show store and last_sale.
SELECT store, MAX(order_date) AS last_sale FROM orders GROUP BY store HAVING MAX(order_date) < DATE '2026-03-31' - INTERVAL '7 days'
5. Which customers ordered in February but haven't ordered since 1 March? Show customer_id.
SELECT DISTINCT customer_id FROM orders WHERE order_date < DATE '2026-03-01' AND customer_id NOT IN (SELECT customer_id FROM orders WHERE order_date >= DATE '2026-03-01')

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed