Marketing wants to know the average order size for repeat customers so they can personalise promotional messaging. Using the customers and orders tables, return id, name, and avg_order_value (rounded to 2 decimal places) for customers who have placed more than one order, ordered by avg_order_value descending.
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-01 |
| 2 | 1 | 200.00 | 2024-01-15 |
| 3 | 1 | 300.00 | 2024-02-01 |
| 4 | 2 | 50.00 | 2024-01-10 |
| 5 | 3 | 80.00 | 2024-01-20 |
| 6 | 3 | 120.00 | 2024-02-05 |
| id | name | avg_order_value |
|---|---|---|
| 1 | Alice | 200.00 |
| 3 | Carol | 100.00 |
Alice has 3 orders (100 + 200 + 300) averaging 200.00. Carol has 2 orders (80 + 120) averaging 100.00. Bob has only 1 order and is excluded by the HAVING COUNT(*) > 1 filter.