Customer First Order, Last Order, and Lifespan
Return one row per customer with first order, last order, lifespan days, and total orders.
- Joins
- Aggregation
- Date analysis
- Type conversion
- Sorting
Exercise brief
Understand the request
Lifecycle marketing analyst Customer maturity reporting needs first order, last order, and observed purchasing lifespan.
Customer maturity reporting needs first order, last order, and observed purchasing lifespan. Return one row per customer with first order, last order, lifespan days, and total orders.
Return
- Return customer_id, customer_name, first_order, last_order, lifespan_days, total_orders in this exact left-to-right order.
Constraints
- Aggregate at customer grain.
- Calculate lifespan from MAX(order_date) minus MIN(order_date).
- Order by lifespan descending, then customer_id.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
customers
customer_idINTEGERcustomer_nameTEXT
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATE
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
MIN(order_date) = first order; MAX(order_date) = last order, per customer.
Hint 2
lifespan_days = day-difference between the MAX and MIN order dates.
Hint 3
COUNT(*) within the group gives total orders.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT c.customer_id, c.customer_name, MIN(o.order_date) AS first_order, MAX(o.order_date) AS last_order, CAST(julianday(MAX(o.order_date)) - julianday(MIN(o.order_date)) AS INTEGER) AS lifespan_days, COUNT(*) AS total_orders FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.customer_name ORDER BY lifespan_days DESC, c.customer_id;Why this works
Customer lifespan (first→last order span) is a key engagement metric. It combines MIN/MAX aggregation with a day-difference over those aggregates. The aggregation is identical everywhere; only the day-difference of the two aggregated dates differs by engine.
Success check
Every customer with orders appears once and single-day lifespans evaluate to zero.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_id | customer_name | first_order | last_order | lifespan_days | total_orders |
|---|---|---|---|---|---|
| 3 | Bob Williams | 2023-01-10 | 2024-08-05 | 573 | 4 |
| 2 | Alice Johnson | 2023-02-15 | 2024-09-01 | 564 | 5 |
| 1 | John Smith | 2024-01-15 | 2024-11-20 | 310 | 5 |
| 5 | David Brown | 2024-04-10 | 2024-12-05 | 239 | 3 |
| 4 | Carol Davis | 2024-03-15 | 2024-10-12 | 211 | 3 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.