Stores Beating Their City's Average in Q1 — SQL Problem
For the quarterly review, a store's Q1 2025 revenue is the sum of its takings dated from 2025-01-01 to 2025-03-31.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Window Functions
- Dialect: MySQL
- Problem: #390
Problem statement
For the quarterly review, a store's Q1 2025 revenue is the sum of its takings dated from 2025-01-01 to 2025-03-31. Compare each store with the average Q1 revenue of the stores in the same city — averaged over the stores of that city that have any Q1 takings.
Return store_name, city and q1_revenue for every store whose Q1 revenue is strictly greater than its city's average. A store alone in its city equals the average and is never returned. Order the rows by city, then by q1_revenue highest first, then by store_name.
Tables
Table: Store
| Column | Type |
|---|---|
| store_id | int |
| store_name | varchar |
| city | varchar |
Primary key: store_id.
Store names are unique.
Table: DailyTakings
| Column | Type |
|---|---|
| store_id | int |
| takings_date | date |
| takings | int |
Primary key: store_id, takings_date.
A store's banked takings for one day, in rupees.
Examples
Example 1
Store
| store_id | store_name | city |
|---|---|---|
| 1 | Indiranagar | Bengaluru |
| 2 | Jayanagar | Bengaluru |
| 3 | Whitefield | Bengaluru |
| 4 | Andheri West | Mumbai |
| 5 | Powai | Mumbai |
| 6 | Kothrud | Pune |
DailyTakings
| store_id | takings_date | takings |
|---|---|---|
| 1 | 2025-01-14 | 210000 |
| 1 | 2025-03-02 | 95000 |
| 2 | 2025-02-10 | 180000 |
| 3 | 2025-03-31 | 150000 |
| 3 | 2025-04-01 | 400000 |
| 4 | 2025-01-20 | 260000 |
| 5 | 2025-02-25 | 260000 |
| 6 | 2025-03-18 | 120000 |
Output
| store_name | city | q1_revenue |
|---|---|---|
| Indiranagar | Bengaluru | 305000 |
How to solve Stores Beating Their City's Average in Q1
The comparison is between a store's quarter and its city's typical store, so the work is layered. Step one: total each store's takings inside the quarter (the date filter is in WHERE, so takings on 2024-12-31 or 2025-04-01 never count). Step two: average those store totals per city — averaging the raw daily rows instead would weight stores by how many days they banked.
The solution names step one as a CTE and compares each row with a correlated AVG over the same city. A store with no Q1 takings has no row in the CTE, so it neither appears nor drags its city's average down, as the statement specifies. A store alone in its city equals its own average and fails the strict >.
The window alternative nests the aggregate in a window, AVG(SUM(takings)) OVER (PARTITION BY city), which computes the city average right next to each store's total in a single grouping pass. The third version builds a per-city table and joins it back. All are linear after the initial grouping.
Reference solution (MySQL)
WITH q1 AS (
SELECT s.store_id, s.store_name, s.city, SUM(t.takings) AS q1_revenue
FROM Store s
JOIN DailyTakings t ON t.store_id = s.store_id
WHERE t.takings_date BETWEEN '2025-01-01' AND '2025-03-31'
GROUP BY s.store_id, s.store_name, s.city
)
SELECT store_name, city, q1_revenue
FROM q1 a
WHERE q1_revenue > (SELECT AVG(b.q1_revenue) FROM q1 b WHERE b.city = a.city)
ORDER BY city, q1_revenue DESC, store_nameAnother way
SELECT store_name, city, q1_revenue FROM (
SELECT s.store_name, s.city, SUM(t.takings) AS q1_revenue,
AVG(SUM(t.takings)) OVER (PARTITION BY s.city) AS city_avg
FROM Store s JOIN DailyTakings t ON t.store_id = s.store_id
WHERE t.takings_date >= '2025-01-01' AND t.takings_date < '2025-04-01'
GROUP BY s.store_id, s.store_name, s.city) x
WHERE q1_revenue > city_avg
ORDER BY city, q1_revenue DESC, store_nameAnother way
WITH q1 AS (SELECT store_id, SUM(takings) AS rev FROM DailyTakings WHERE QUARTER(takings_date) = 1 AND YEAR(takings_date) = 2025 GROUP BY store_id),
c AS (SELECT s.city, AVG(q.rev) AS avg_rev FROM q1 q JOIN Store s ON s.store_id = q.store_id GROUP BY s.city)
SELECT s.store_name, s.city, q.rev AS q1_revenue FROM q1 q JOIN Store s ON s.store_id = q.store_id JOIN c ON c.city = s.city
WHERE q.rev > c.avg_rev ORDER BY s.city, q.rev DESC, s.store_name← Warehouse Transfers Still in Transit · FIFO Valuation of the Stock Left on Hand →