# Revenue by Customer City — SQL Interview Question

Aced (formerly Exponent) publishes a public search API over its indexed pages; the guide is at https://www.aced.io/agents/agent-access.

Source: https://www.aced.io/questions/4378/revenue-by-customer-city
Source observed: 2026-11-04T23:27:49.570Z
Last checked: 2026-11-04T23:27:49.57+00:00
Indexed: 2026-11-06T09:47:25.317+00:00

---

# Revenue by Customer City

We provide interview preparation through Aced (formerly Exponent), including practice questions for data analyst roles. This medium-difficulty coding question asks you to write a SQL solution that calculates total transaction revenue for each user city, converts revenue to USD, and orders cities by descending revenue.

## Question details

- **Role:** [Data Analyst](https://www.aced.io/questions?role=data-analyst)
- **Categories:** [Coding](https://www.aced.io/questions?type=coding), [Data Analysis](https://www.aced.io/questions?type=data-analysis)
- **Format:** Interview coding question
- **Community answers:** No answers have been contributed by the community.
- **Solutions:** Detailed company statistics and the solution are available through [Upgrade](https://www.aced.io/upgrade?src=questions).

## Database schema

The supplied schema contains these tables and relationships:

```text
users                                    products
+---------------+---------+              +-----------------+---------+
| id            | int     |        +-----| id              | int     |
| first_name    | varchar |        |     | name            | varchar |
| last_name     | varchar |        |     +->| product_line_id | date    |
| user_city     | int     |        |  |  | stock           | int     |
| email         | int     |        |  +-----------------+---------+
+---------------+---------+        |
        |                         |
        |                         |
        |     transactions        |  product_lines
        |     +---------------+---------+   +--------+--------+
        +-----| id            | int     |<----+  | id     | int    |
              | customer_id   | int     |           | name   | varchar|
              | product_id    | int     |           +--------+--------+
              | amount        | int     |
              | currency_code | varchar |
              | date          | date    |
              +---------------+---------+

exchange_rate
+----------------------+---------+
| id                   | int     |
| source_currency_code | varchar |
| target_currency_code | varchar |
| rate                 | numeric  |
+----------------------+---------+
```

Use the relationships implied by the schema to associate each transaction with its customer in `users`, determine the customer's `user_city`, and apply the relevant exchange rate when calculating USD revenue.

## Required result

Return one row per user city, with the total revenue converted to USD and sorted from highest to lowest revenue:

```text
user_city  | total_revenue
-----------+------------------------
varchar   | float (no need to round)
```

The result should therefore contain the city and its aggregated USD revenue; rounding is not required.

## Related questions

- [Sales by Customer City](https://www.aced.io/questions/3048/sales-by-city)
- [Top Customer by Orders](https://www.aced.io/questions/3973/top-customer-by-orders)
- [Find Top Customer by Year](https://www.aced.io/questions/3977/find-top-customer-by-year)

## Related courses

### [Data Analyst Interview Prep](https://www.aced.io/courses/data-analytics)

![Data Analyst Interview Prep](https://images.ctfassets.net/x78yjrjc11pq/72OTqij57b2TklhaD2vrdV/be0dae6242904970d1696e5a4d858e18/AdobeStock_1357126576.jpeg?w=800&h=800&fm=webp&q=75)

Our Data Analyst Interview Prep course supports product, marketing, and business analyst interviews. It includes real take-home case studies, technical and dashboarding practice, and strategies from interviewers at technology companies and startups.

### [SQL Interviews](https://www.aced.io/courses/sql-interviews)

![SQL Interviews](https://images.ctfassets.net/x78yjrjc11pq/62mN54S54HpLIOmyG8qbnq/54deaf5e397d0807d71c0e27e21e5ee3/sql_illustration_2.jpg?w=800&h=800&fm=webp&q=75)

Our SQL Interviews course covers syntax fundamentals, aggregations, window functions, data manipulation, complex queries, and analysis across diverse datasets.

## Practice access

[Sign up to run code](https://www.aced.io/signup?src=lesson) in the embedded practice environment. The available interface includes an editor, output and test-results tabs, and a console.

---

[Read this page’s summary](https://www.aced.io/agents/api/public/v1/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F4378%2Frevenue-by-customer-city&trace=t_679ff9ab98f3)

## Ask a follow-up about this page

Need a more specific answer? Ask the exact question you have. It returns a fresh, cited answer from Aced (formerly Exponent)’s knowledge base. No account or API key required.

Open this URL with your own question, URL-encoded. Include your question in q. All research inputs are optional; no account or setup.

`GET https://www.aced.io/agents/agent/faq?q={question}&trace=t_679ff9ab98f3`

[Tool guide and examples](https://www.aced.io/agents/agent-access) · [OpenAPI](https://www.aced.io/agents/api/public/v1/openapi.json) · [Page TLDR form](https://www.aced.io/agents/agent-tools/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F4378%2Frevenue-by-customer-city). Opening a form does not execute a tool.