Why is one joint sales leader missing from the ranking?

Prepare the diagnostic input

The stated decision is to identify every top-selling agent in each team. ROW_NUMBER picks exactly one row even when two agents tie. RANK should order only by the measure that defines the tie; adding the agent ID inside that rank would break the business tie again.

Use a fresh SQLite test database for the complete setup below. Every record is fictional; quantities are units and input monetary columns ending in cents use integer currency cents. Calculated averages may include fractions of a cent. Input previews include every table in this small fixture.

CREATE TABLE agent_totals(agent TEXT PRIMARY KEY,team TEXT,sales_cents INTEGER);
INSERT INTO agent_totals VALUES ('A','North',50000),('B','North',50000),('C','North',30000);

Reproduce the misleading result and correct it

WITH ranked AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY team ORDER BY sales_cents DESC,agent) AS place FROM agent_totals)
SELECT team,agent,sales_cents FROM ranked WHERE place=1 ORDER BY team,agent;

WITH ranked AS (SELECT *,RANK() OVER(PARTITION BY team ORDER BY sales_cents DESC) AS place FROM agent_totals)
SELECT team,agent,sales_cents FROM ranked WHERE place=1 ORDER BY team,agent;

A and B each sell 50000 cents and both lead North. The bad query chooses A through its ID tie-break; the correction lists both. The final display can still sort their names without changing their rank.

Check the boundary before applying the fix

Add D at 60000 cents. D alone is now first and the former leaders leave the top group. The query recognises ties, not a fixed number of winners.

For the boundary test, recreate the complete setup in another fresh synthetic database, apply only the change below, then rerun the corrected query. Do not apply a test mutation to a live system.

INSERT INTO agent_totals VALUES ('D','North',60000);

No commission or incentive entitlement is calculated. Eligible sales, returns and measurement period must be agreed before any operational reward decision.

The displayed bad, corrected and boundary results were executed in SQLite and checked against separately authored expected rows. This demonstrates the stated fixture only; it does not test other database engines, production performance or your live schema.