Lessons › SQL › Go further in SQL: Business questions

SQL lesson

Of February's new customers, how many came back?

Of the customers whose first order was in February, how many ordered again in March? That's retention. First find each customer's first order, keep the February ones, then count how many of them appear in March.

Example

Retention: first month, then check later

WITH firsts AS (
  SELECT customer_id, MIN(order_date) AS first_order
  FROM orders
  GROUP BY customer_id
), feb_new AS (
  SELECT customer_id
  FROM firsts
  WHERE first_order < '2026-03-01'
)
SELECT COUNT(*) AS new_customers,
       SUM(CASE WHEN customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01') THEN 1 ELSE 0 END) AS came_back
FROM feb_new

Result:

Step by step

1. Two counts side by side.

   WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id), feb_new AS (SELECT customer_id FROM firsts WHERE first_order < '2026-03-01') SELECT COUNT(*) AS new_customers, SUM(CASE WHEN customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01') THEN 1 ELSE 0 END) AS came_back FROM feb_new

2. No join.

   WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id), feb_new AS (SELECT customer_id FROM firsts WHERE first_order < '2026-03-01') SELECT COUNT(*) AS new_customers, SUM(CASE WHEN customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01') THEN 1 ELSE 0 END) AS came_back FROM feb_new

3. MIN(order_date) < '2026-03-01'.

   WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id), feb_new AS (SELECT customer_id FROM firsts WHERE first_order < '2026-03-01') SELECT COUNT(*) AS new_customers, SUM(CASE WHEN customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01') THEN 1 ELSE 0 END) AS came_back FROM feb_new

4. Two CTEs, then SUM(CASE … IN (March customers)).

   WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id), feb_new AS (SELECT customer_id FROM firsts WHERE first_order < '2026-03-01') SELECT COUNT(*) AS new_customers, SUM(CASE WHEN customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01') THEN 1 ELSE 0 END) AS came_back FROM feb_new

Practice

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

WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id), feb_new AS (SELECT customer_id FROM firsts WHERE first_order < '2026-03-01') SELECT COUNT(*) AS new_customers, SUM(CASE WHEN customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01') THEN 1 ELSE 0 END) AS came_back FROM feb_new

WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id), feb_new AS (SELECT customer_id FROM firsts WHERE first_order < '2026-03-01') SELECT 1.0 * SUM(CASE WHEN customer_id IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01') THEN 1 ELSE 0 END) / COUNT(*) AS retention FROM feb_new

SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id

WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id) SELECT SUBSTRING(first_order, 1, 7) AS month, COUNT(*) AS new_customers FROM firsts GROUP BY SUBSTRING(first_order, 1, 7)

WITH firsts AS (SELECT customer_id, MIN(order_date) AS first_order FROM orders GROUP BY customer_id) SELECT customer_id FROM firsts WHERE first_order < '2026-03-01' AND customer_id NOT IN (SELECT customer_id FROM orders WHERE order_date >= '2026-03-01')