Each Broker's Best Month by Deal Value

Medium
364.

Each Broker's Best Month by Deal Value

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

ColumnType
deal_idint
broker_namevarchar
closed_ondate
deal_valueint

Primary key: deal_id.

deal_value is the transaction value in rupees.

Example

The rows must come back in the order shown.

ClosedDeal

deal_idbroker_nameclosed_ondeal_value
1Kumar Estates2024-11-039500000
2Kumar Estates2024-11-214500000
3Kumar Estates2024-12-0914000000
4Prime Homes2024-10-157200000
5Prime Homes2025-01-113100000
6Prime Homes2025-01-285200000
7Urban Keys2024-12-308800000

Output

broker_namebest_monthmonth_valuedeals_in_month
Kumar Estates2024-11140000002
Prime Homes2025-0183000002
Urban Keys2024-1288000001
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.

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

ColumnType
deal_idint
broker_namevarchar
closed_ondate
deal_valueint

Primary key: deal_id.

deal_value is the transaction value in rupees.

Examples

Example 1

ClosedDeal

deal_idbroker_nameclosed_ondeal_value
1Kumar Estates2024-11-039500000
2Kumar Estates2024-11-214500000
3Kumar Estates2024-12-0914000000
4Prime Homes2024-10-157200000
5Prime Homes2025-01-113100000
6Prime Homes2025-01-285200000
7Urban Keys2024-12-308800000

Output

broker_namebest_monthmonth_valuedeals_in_month
Kumar Estates2024-11140000002
Prime Homes2025-0183000002
Urban Keys2024-1288000001

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_name

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

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