SQL interview question: First and Last Order per Customer
Customer Order Bookends
Analytics wants to compare each customer's first and last purchase to understand behavior evolution.
Show each customer's first order date and last order date, along with days between them.
Return customer_id, first_order, last_order, and days_between (a whole number of days). Use JULIANDAY for date arithmetic.
Skills: MIN/MAX with GROUP BY and date arithmetic
Tables
orders 15 rows
- order_id integer PK
- customer_id integer → customers
- order_date text
- total_amount real
- status text
| order_id | customer_id | order_date | total_amount | status |
|---|---|---|---|---|
| 1 | 101 | 2024-01-20 | 249.99 | delivered |
| 2 | 101 | 2024-03-15 | 1999.99 | delivered |
| 4 | 101 | 2024-09-05 | 79.99 | delivered |