Stores Beating Their City's Average in Q1

Medium
390.

Stores Beating Their City's Average in Q1

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

Store names are unique.

Table: DailyTakings

ColumnType
store_idint
takings_datedate
takingsint

Primary key (together): store_id, takings_date.

A store's banked takings for one day, in rupees.

Example

The rows must come back in the order shown.

Store

store_idstore_namecity
1IndiranagarBengaluru
2JayanagarBengaluru
3WhitefieldBengaluru
4Andheri WestMumbai
5PowaiMumbai
6KothrudPune

DailyTakings

store_idtakings_datetakings
12025-01-14210000
12025-03-0295000
22025-02-10180000
32025-03-31150000
32025-04-01400000
42025-01-20260000
52025-02-25260000
62025-03-18120000

Output

store_namecityq1_revenue
IndiranagarBengaluru305000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
store_idint
store_namevarchar
cityvarchar

Primary key: store_id.

Store names are unique.

Table: DailyTakings

ColumnType
store_idint
takings_datedate
takingsint

Primary key: store_id, takings_date.

A store's banked takings for one day, in rupees.

Examples

Example 1

Store

store_idstore_namecity
1IndiranagarBengaluru
2JayanagarBengaluru
3WhitefieldBengaluru
4Andheri WestMumbai
5PowaiMumbai
6KothrudPune

DailyTakings

store_idtakings_datetakings
12025-01-14210000
12025-03-0295000
22025-02-10180000
32025-03-31150000
32025-04-01400000
42025-01-20260000
52025-02-25260000
62025-03-18120000

Output

store_namecityq1_revenue
IndiranagarBengaluru305000

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_name

Another 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_name

Another 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 →