Finance wants revenue broken down by user, including those with zero orders. Using the users and orders tables, return user_id, name, and total_revenue (sum of order amounts, 0 if no orders), ordered by total_revenue descending then name ascending.
users
| column | type |
|---|---|
| id | INTEGER |
| name | TEXT |
| TEXT |
orders
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| amount | NUMERIC |
| created_at | DATE |
users
| id | name | |
|---|---|---|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
| 3 | Carol | carol@example.com |
orders
| id | user_id | amount | created_at |
|---|---|---|---|
| 1 | 1 | 200.00 | 2024-01-01 |
| 2 | 1 | 150.00 | 2024-02-01 |
| 3 | 2 | 100.00 | 2024-01-15 |
| user_id | name | total_revenue |
|---|---|---|
| 1 | Alice | 350.00 |
| 2 | Bob | 100.00 |
| 3 | Carol | 0 |
Alice has two orders totalling 350. Bob has one order for 100. Carol has no orders — the LEFT JOIN keeps her in the result and COALESCE converts her NULL aggregate to 0.