Each Broker's Best Month by Deal Value — SQL Problem
A brokerage's annual awards name each broker's best month: the calendar month ('YYYY-MM') in which the broker's closed deals added up to the highest total…
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #364
Problem statement
A brokerage's annual awards name each broker's best month: the calendar month ('YYYY-MM') in which the broker's closed deals added up to the highest total deal_value. If two months tie, the earlier month wins.
Return broker_name, best_month, month_value (that month's total) and deals_in_month, one row per broker who closed any deal, ordered by broker_name.
Tables
Table: ClosedDeal
| Column | Type |
|---|---|
| deal_id | int |
| broker_name | varchar |
| closed_on | date |
| deal_value | int |
Primary key: deal_id.
deal_value is the transaction value in rupees.
Examples
Example 1
ClosedDeal
| deal_id | broker_name | closed_on | deal_value |
|---|---|---|---|
| 1 | Kumar Estates | 2024-11-03 | 9500000 |
| 2 | Kumar Estates | 2024-11-21 | 4500000 |
| 3 | Kumar Estates | 2024-12-09 | 14000000 |
| 4 | Prime Homes | 2024-10-15 | 7200000 |
| 5 | Prime Homes | 2025-01-11 | 3100000 |
| 6 | Prime Homes | 2025-01-28 | 5200000 |
| 7 | Urban Keys | 2024-12-30 | 8800000 |
Output
| broker_name | best_month | month_value | deals_in_month |
|---|---|---|---|
| Kumar Estates | 2024-11 | 14000000 | 2 |
| Prime Homes | 2025-01 | 8300000 | 2 |
| Urban Keys | 2024-12 | 8800000 | 1 |
How to solve Each Broker's Best Month by Deal Value
Two steps. First aggregate to one row per broker per month: DATE_FORMAT(closed_on, '%Y-%m') buckets the dates, and SUM/COUNT give the month's value and number of deals. Then pick the top month per broker with ROW_NUMBER() OVER (PARTITION BY broker_name ORDER BY month_value DESC, month) and keep row 1.
The second sort key is the tie rule. Kumar Estates closed ₹1.4 crore in November (two deals) and ₹1.4 crore in December (one deal): the totals tie, and ordering by month ascending makes November rank first. Without it, ROW_NUMBER would choose arbitrarily and the answer would change with storage order; with RANK both months would come back.
The window-free alternative keeps a month when no other month of the same broker is better — a higher total, or the same total earlier — via NOT EXISTS. Either way the grouped CTE is small: one row per broker-month.
Reference solution (MySQL)
WITH monthly AS (
SELECT broker_name,
DATE_FORMAT(closed_on, '%Y-%m') AS month,
SUM(deal_value) AS month_value,
COUNT(*) AS deals_in_month
FROM ClosedDeal
GROUP BY broker_name, DATE_FORMAT(closed_on, '%Y-%m')
), ranked AS (
SELECT m.*, ROW_NUMBER() OVER (PARTITION BY broker_name ORDER BY month_value DESC, month) AS rn
FROM monthly m
)
SELECT broker_name, month AS best_month, month_value, deals_in_month
FROM ranked
WHERE rn = 1
ORDER BY broker_nameAnother way
WITH monthly AS (SELECT broker_name, LEFT(closed_on, 7) AS month, SUM(deal_value) AS month_value, COUNT(*) AS deals_in_month FROM ClosedDeal GROUP BY broker_name, LEFT(closed_on, 7)) SELECT a.broker_name, a.month AS best_month, a.month_value, a.deals_in_month FROM monthly a WHERE NOT EXISTS (SELECT 1 FROM monthly b WHERE b.broker_name = a.broker_name AND (b.month_value > a.month_value OR (b.month_value = a.month_value AND b.month < a.month))) ORDER BY a.broker_nameAnother way
WITH monthly AS (SELECT broker_name, SUBSTRING(closed_on, 1, 7) AS month, SUM(deal_value) AS month_value, COUNT(*) AS deals_in_month FROM ClosedDeal GROUP BY broker_name, SUBSTRING(closed_on, 1, 7)), best AS (SELECT broker_name, MAX(month_value) AS top FROM monthly GROUP BY broker_name) SELECT m.broker_name, MIN(m.month) AS best_month, MAX(m.month_value) AS month_value, MIN(m.deals_in_month) AS deals_in_month FROM monthly m JOIN best b ON b.broker_name = m.broker_name AND b.top = m.month_value WHERE m.month = (SELECT MIN(x.month) FROM monthly x WHERE x.broker_name = m.broker_name AND x.month_value = b.top) GROUP BY m.broker_name ORDER BY m.broker_name← Co-Living Bed Occupancy by Property · Towers Where Every Unit Cleared March Dues →