# Sales 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/3048/sales-by-city
Source observed: 2026-11-04T21:34:32.514Z
Last checked: 2026-11-04T21:34:32.514+00:00
Indexed: 2026-11-05T01:37:28.391+00:00

---

# Sales by Customer City

**Medium · Coding · SQL**

We provide interview-practice questions and an in-browser SQL editor through Aced (formerly Exponent). This question asks you to write a query that returns the number of transactions for each customer city, ordered from the city with the most transactions to the city with the fewest.

## Question

Write a query to fetch the number of transactions per user city, ordered by descending number of transactions, using the following e-commerce schema.

### Schema

```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 |
+----------------------+---------+
```

The expected result format is:

```text
user_city | number_of_transactions
-----------+------------------------
varchar   | int
```

## Community solutions and discussion

### Yash B. — October 26, 2026

[Yash B.](https://www.aced.io/users/0c8f33fa-aec8-4243-a2ab-218465bb4355) · Member · [367 karma](https://www.aced.io/karma "367 karma")

```sql
Select u.user_city as user_city, count(*) as number_of_transactions
from users as u
join transactions as t on u.id = t.customer_id
group by u.user_city
order by number_of_transactions Desc
```

### Steven C. — July 13, 2026

[Steven C.](https://www.aced.io/users/bb612b23-2c4c-4d8b-acf0-adc084c8f4bf) · Member · [98 karma](https://www.aced.io/karma "98 karma")

Steven notes that the answer and diagram may not be correct because the diagram appears to show a relationship between `transactions.id` and `users.id`, rather than `transactions.customer_id`.

### Malak M. — July 18, 2026

[Malak M.](https://www.aced.io/users/515572de-e939-4d9a-821f-2052fd7eb0a8) · [118 karma](https://www.aced.io/karma "118 karma")

```sql
SELECT u.user_city, COUNT(t.customer_id) as number_of_transactions
FROM users as u
JOIN transactions as t ON u.id = t.customer_id
GROUP BY u.user_city
ORDER BY number_of_transactions DESC
```

### Ray C. — Updated January 16, 2026

[Ray C.](https://www.aced.io/users/6820360d-94ec-4b8e-b0a7-0b1ed204a231) · Member · [386 karma](https://www.aced.io/karma "386 karma")

```sql
select u.user_city, count(t.id) as number_of_transactions
from users u
join transactions t ON u.id = t.customer_id
group by u.user_city
order by number_of_transactions desc
```

### M K. — November 16, 2025

[M K.](https://www.aced.io/users/f03e2fe7-f82d-4fdb-8412-384a28a31746) · Member · [225 karma](https://www.aced.io/karma "225 karma")

M K. notes that the arrow from `users` to `transactions` should point to `customer_id`, not `id`, because each `customer_id` belongs to a user through `users.id`.

## Related questions

- [Revenue by Customer City](https://www.aced.io/questions/4378/revenue-by-customer-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)

[Browse all questions](https://www.aced.io/questions)

The SQL editor includes Editor, Share, Output, Test Results, and Console views. [Sign up to run code](https://www.aced.io/signup?src=lesson). Detailed company statistics for this question require [upgrading](https://www.aced.io/upgrade?src=questions).

---

[Read this page’s summary](https://www.aced.io/agents/api/public/v1/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F3048%2Fsales-by-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%2F3048%2Fsales-by-city). Opening a form does not execute a tool.