Finance wants to compare customers' first and most recent purchase amounts to see if spending has changed over time. A rising last-purchase amount relative to the first suggests customers are buying more expensive items as they become loyal — a key signal for lifetime value models. Using the orders table, return customer_id, first_purchase_amount, last_purchase_amount, first_date, and last_date, ordered by customer_id. Customers with only one order should have the same value for first and last.
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
orders
| id | customer_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 50.00 | 2024-01-10 |
| 2 | 1 | 150.00 | 2024-03-05 |
| 3 | 1 | 200.00 | 2024-06-15 |
| 4 | 2 | 300.00 | 2024-02-20 |
| customer_id | first_purchase_amount | last_purchase_amount | first_date | last_date |
|---|---|---|---|---|
| 1 | 50.00 | 200.00 | 2024-01-10 | 2024-06-15 |
| 2 | 300.00 | 300.00 | 2024-02-20 | 2024-02-20 |
Customer 1 has three orders; the earliest (Jan 10) was 50.00 and the latest (Jun 15) was 200.00. Customer 2 has one order, so first and last amounts are both 300.00 and both dates are the same.