Finance needs to identify the highest-value customers to prioritise account management resources. Using the customers and orders tables, calculate the total amount spent by each customer and return id, name, and lifetime_value, ordered from highest to lowest.
customers
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| TEXT |
orders
| column | type |
|---|---|
| id | INTEGER |
| customer_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
customers
| id | name | |
|---|---|---|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
| 3 | Carol | carol@example.com |
orders
| id | customer_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 100.00 | 2024-01-10 |
| 2 | 1 | 250.00 | 2024-02-15 |
| 3 | 2 | 75.00 | 2024-03-01 |
| 4 | 3 | 500.00 | 2024-03-10 |
| 5 | 3 | 125.00 | 2024-04-05 |
| id | name | lifetime_value |
|---|---|---|
| 3 | Carol | 625.00 |
| 1 | Alice | 350.00 |
| 2 | Bob | 75.00 |
Carol spent 500 + 125 = 625. Alice spent 100 + 250 = 350. Bob spent 75 in a single order.