Revenue per City
An order is for qty units of one product. Return each city that has at least one order, with revenue: the sum over its customers' orders of qty times the product's price. Rows may come in any order.
Example 1
customers
| id | name | city |
|---|---|---|
| 1 | Ada | Austin |
| 2 | Ben | Boston |
| 3 | Cy | Austin |
| 4 | Dee | Denver |
orders
| id | customer_id | product_id | qty |
|---|---|---|---|
| 1 | 1 | 10 | 2 |
| 2 | 3 | 11 | 1 |
| 3 | 2 | 10 | 5 |
products
| id | name | price |
|---|---|---|
| 10 | pen | 1.5 |
| 11 | ink | 8.0 |
returns
| city | revenue |
|---|---|
| Austin | 11.0 |
| Boston | 7.5 |
Constraints
- Up to 6 customers, 5 products and 12 orders
- Every order points at a customer and a product in the tables
Write it in your browser, against the clock. The model query, with a note on each piece of it, comes after.
More SQL problems
- Running BalanceMedium
- Second From the TopMedium
- Share of the TotalMedium
- Volume Weighted PriceMedium
- Chain of CommandHard
- Daily BarsHard
- Longest Winning StreakHard
- Median SalaryHard