Revenue by City
Marketing at Whetcode Supply has a budget for local advertising and one decision to make with it: which cities to spend it in. The measure they trust is money, not headcount — a city with one customer who buys desks is worth more than a city with three who buy notebooks.
Getting there needs all three tables. The city is recorded against the customer, the quantity against the order, and the price sits only in the catalogue. A single row of this report draws one fact from each.
customers — one row per registered customer. city is the city given at signup and signup_date is the day they registered, written year-month-day.
| id | name | city | signup_date |
|---|---|---|---|
| 1 | Priya Nair | Austin | 2022-01-15 |
| 2 | Tom Becker | Berlin | 2022-03-02 |
| 3 | Sofia Rossi | Milan | 2022-05-19 |
| 4 | Liam OConnor | Dublin | 2023-01-08 |
| 5 | Wei Zhang | Austin | 2023-04-27 |
products — the catalogue, one row per item on sale. price is what a single unit costs, in dollars.
| id | name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | electronics | 25.0 |
| 2 | Standing Desk | furniture | 350.0 |
| 3 | Desk Lamp | furniture | 40.0 |
| 4 | Mechanical Keyboard | electronics | 85.0 |
| 5 | Notebook Set | office | 12.0 |
orders — one row per order. customer_id is the id of the customer who placed it, product_id the id of the item bought, quantity how many units of that item, and order_date the day it was placed. An order row covers one item only.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
The tables already exist in the database — there is nothing to create or load.
Task: Write a query returning two columns, city and revenue (the value of every order placed by customers in that city added together, each order being worth its item's price times its number of units, with the city's total rounded to two decimal places), one row per city that has produced at least one order, biggest earner first. No two cities tie. A city whose customers have never ordered does not appear at all.
Example output — shape only, on invented cities and totals:
| city | revenue |
|---|---|
| Lisbon | 2140 |
| Osaka | 1360 |
| Utrecht | 590 |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in