Write a query to find the first purchase date and last purchase date for every customer.

Answer

Group by customer and take the earliest and latest order date using MIN() and MAX() together.

Solution

SELECT customer_id,
       MIN(order_date) AS first_purchase_date,
       MAX(order_date) AS last_purchase_date
FROM Orders
GROUP BY customer_id;

Step by Step

1. GROUP BY customer_id collapses all of a customer's orders into a single row.
2. MIN(order_date) across that group is the earliest date - the first purchase.
3. MAX(order_date) across the same group is the latest date - the most recent purchase.
4. Both aggregates are computed in a single pass over the same grouped rows, no extra joins needed.

Alternative Method: Window Functions with DISTINCT

SELECT DISTINCT customer_id,
       FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS first_purchase_date,
       FIRST_VALUE(order_date) OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS last_purchase_date
FROM Orders;

This achieves the same result using FIRST_VALUE() with two different orderings, useful when the query already needs other row-level, non-aggregated columns alongside these two dates in the same result set - something a plain GROUP BY can't easily provide without additional joins.

Key Exam Traps

• Customers with a single order: MIN() and MAX() correctly return the same date for both columns - not an edge case that needs special handling.
• NULL order dates: Both MIN() and MAX() automatically ignore NULL values, so a customer with some orders missing a date still gets a correct result from their valid dates.
• Customers with zero orders don't appear at all in this query, since there's nothing to group - if the requirement includes every customer regardless of order history, a LEFT JOIN from Customers would be needed instead of grouping Orders alone.