IntermediateSQL · 25 min

Top N Per Group: Intermediate

You can write the ROW_NUMBER + filter shape from memory. The interviewer knows that. What they are testing now is whether you understand the business semantics behind the choice. Top N per group is the question that pivots from 'can you write window functions' to 'can you choose between ROW_NUMBER, RANK, and DENSE_RANK based on what the consumer of the report actually wants.' The query is twelve lines either way. The difference is whether you ask the interviewer one clarifying question before writing the SQL, and whether the answer you write defends the business rule rather than just returning rows.

Treat the top-N-per-group query as a business semantics question, not a syntax question

Pick between ROW_NUMBER, RANK, and DENSE_RANK based on the tie-handling contract

Anticipate the ties-and-NULLs follow-ups before the interviewer asks them

Articulate the tradeoff in one sentence the interviewer can carry to the debrief

The ROW_NUMBER + Filter Trick

Daily Life

Interviews

Identify when a question requires per-group ranking instead of a global ORDER BY LIMIT.

The shape of the question rarely changes. 'Top 3 highest-paid employees per department.' 'Top 5 best-selling products per category.' 'Top 10 most-recent orders per customer.' What changes is what 'top 3' actually means when the data does not cooperate. Two employees tied for second place. A category with only two products. A NULL salary on a row that should be ignored. Every one of those edge cases changes the right query. The interviewer at this level is watching whether you anticipate the edge case and pick the right tool, or whether you write ROW_NUMBER by reflex and have to be corrected.

You are being tested on top N per group when you hear:

▸"top N highest/lowest X per group"
▸"the most-recent N records for each customer"
▸"for every department, return the top performer"
▸"top N including ties" (a tell that the answer is RANK, not ROW_NUMBER)
▸"top N salary levels" (a tell that the answer is DENSE_RANK)

What the interviewer is actually testing

The pattern recognition is no longer the bar. What the interviewer wants to see is whether you ask the clarifying question before reaching for the syntax. The candidate who writes ROW_NUMBER without asking what should happen at ties is producing a query that may or may not match the consumer's intent. The candidate who pauses and asks 'should ties at position N be included or excluded?' before touching the keyboard has named the most important decision in the query. The clarifying question signals that the candidate treats reporting as a contract, not just a query.

•Answer that misses the seam

Writes ROW_NUMBER by reflex, picks an arbitrary tiebreaker silently
Returns a result that happens to match the spec on clean data
Has no answer when the interviewer asks 'what if two employees tie?'
Defends the choice with 'that's what ROW_NUMBER does'

✓Answer that holds up

Asks the tie-handling question before writing SQL
Picks ROW_NUMBER, RANK, or DENSE_RANK based on the answer
Names the tiebreaker column when ROW_NUMBER is the right tool
Defends the choice with the consumer's interpretation, not the syntax

The clarifying question that earns the credit

Before you write SELECT, ask: 'should ties at position N be included or excluded, and is the report counting distinct salary levels or distinct employees?' That single question contains the entire business semantics decision. If the answer is 'include ties,' the tool is RANK. If the answer is 'exclude ties, give me exactly N,' the tool is ROW_NUMBER plus a deterministic tiebreaker. If the answer is 'top N distinct salary levels, all employees who match,' the tool is DENSE_RANK. Asking the question first turns a syntax question into a design conversation.

The single clarifying question that earns the credit:

▸Should ties at position N be included or excluded?
▸If ties are excluded, what is the tiebreaker rule?
▸Is the report counting distinct values or distinct employees?

Most candidates ask 'what should I do about ties?' after they have written the query and the interviewer points at the output. Asking before writing converts the question from 'fix the bug' to 'design the contract.' That conversion is the move; it changes the conversation from defending code to defining requirements.

Ties, NULLs, and the RANK Trap

Daily Life

Interviews

Write a correct top-N-per-group query using ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) with an outer filter.

The canonical solution still applies. ROW_NUMBER inside a CTE, filtered to rn <= N in the outer query. The shape is unchanged. What changes at this level is how you talk about the tiebreaker. ROW_NUMBER without a deterministic tiebreaker returns arbitrary results on ties, and the interviewer will probe that.

The query with the tiebreaker in place

	/* Top 3 highest-paid employees per department, deterministic tiebreaker */
	WITH ranked AS (
	SELECT
	department_id,
	employee_id,
	salary,
	hire_date,
	ROW_NUMBER() OVER (
	PARTITION BY department_id
	ORDER BY salary DESC, hire_date, employee_id
	) AS rn
	FROM employees
	)

	SELECT
	department_id,
	employee_id,
	salary,
	hire_date
	FROM ranked
	WHERE rn <= 3

Why the multi-column ORDER BY matters

Without a tiebreaker, ROW_NUMBER assigns positions arbitrarily for tied salaries. The same query on the same data can return Bob today and Carol tomorrow, depending on the engine's row order. That non-determinism is the bug that ships to production and is impossible to reproduce. The fix is a multi-column ORDER BY that includes a tiebreaker that is unique per row. Salary DESC is the primary. Hire_date ASC is the business tiebreaker (longer-tenured wins). Employee_id ASC is the absolute tiebreaker (guaranteed unique). The three-column ORDER BY guarantees the same row wins every time.

Each ORDER BY column encodes a business rule:

▸salary DESC: "highest pay wins" (the primary metric)
▸hire_date ASC: "tenure breaks ties" (the tiebreaker)
▸employee_id ASC: "deterministic fallback" (the safety net)

Multi-column ORDER BY inside a window function is the move that distinguishes the candidate who has shipped reporting from the candidate who has not. Shipped reporting means having seen a dashboard where 'top 3' returned different rows on consecutive refreshes; the lesson learned is that ROW_NUMBER without a deterministic tiebreaker is a bug waiting for a test data refresh.

The tiebreaker order also encodes business semantics

'Salary DESC, hire_date ASC' encodes the rule 'on ties, longer-tenured employees win.' 'Salary DESC, performance_rating DESC' encodes 'on ties, higher rating wins.' 'Salary DESC, employee_id ASC' encodes 'on ties, the oldest employee record wins.' The choice of tiebreaker is a business decision. Some teams want longest tenure. Some want highest recent rating. Some want most senior title. Ask which one the consumer wants. The clarifying question converts the SQL into a documented contract.

•Tiebreaker that ships the bug

ORDER BY salary DESC (no tiebreaker)
Results change between refreshes on tied rows
Impossible to reproduce in dev environments
Bug surfaces only after a data refresh in prod

✓Tiebreaker that defends the contract

ORDER BY salary DESC, hire_date ASC, employee_id ASC
Same row wins every time
Business rule is encoded in the column order
The tiebreaker is documented in the query itself

The dialect quirk to know

On some engines (notably BigQuery and certain Hive versions), ROW_NUMBER without a deterministic tiebreaker can produce different results on consecutive runs of the same query against the same data, because the underlying row order can vary with query parallelism. On Postgres and SQL Server, the results are usually stable across a single transaction but not across transactions. Mention this when the interviewer probes determinism: 'on a distributed engine, a non-deterministic tiebreaker can produce different rows on parallel passes, so I always include a unique-id tiebreaker for production queries.'

TIP

When you write the multi-column ORDER BY, narrate the rule each column encodes. 'Salary descending is the primary. Hire date ascending breaks ties in favor of tenure. Employee id is the absolute fallback to guarantee determinism.' Three sentences. Each tells the interviewer that you treated the ORDER BY as design, not as syntax.

ROW_NUMBER vs RANK vs DENSE_RANK

Daily Life

Interviews

Choose between ROW_NUMBER, RANK, and DENSE_RANK based on whether ties should inflate, collapse, or be broken arbitrarily.

This section is where the question stops being about syntax and starts being about which tool to reach for. ROW_NUMBER, RANK, and DENSE_RANK look interchangeable. They are not. Each one answers a different business question, and picking the wrong one produces visibly plausible numbers that disagree with what the consumer asked for. The candidate at this level is being scored on whether they pick the right function from the answer to the clarifying question, not on whether they can recite what each function does.

Three functions, three business questions

ROW_NUMBER when the consumer needs exactly N rows per group and is willing to live with an arbitrary or business-defined tiebreaker. 'Show me the three employees who would receive the top performance bonuses.' That is exactly-N semantics. Ties have to be broken; the multi-column ORDER BY breaks them. The output has N rows per group, no matter how many ties exist.

Business question	Right function	Returns
"Top N performers, exactly N"	ROW_NUMBER + tiebreaker	Exactly N rows per group
"Top N positions including ties"	RANK	≥ N rows per group; gaps after ties
"All employees at the top N salary levels"	DENSE_RANK	≥ N rows per group; no gaps
"Top N with ties broken by performance"	ROW_NUMBER + ORDER BY rating DESC	Exactly N rows per group

The NULLS LAST move

NULLs in the ORDER BY column produce silent ranking bugs. By default, Postgres puts NULLs last when sorting DESC; MySQL puts them first; SQL Server depends on the version. If you ORDER BY salary DESC and some employees have NULL salary (intern, on leave, data quality issue), the engine-default behavior decides whether NULL rows land at the top of the ranking. Always write NULLS LAST or NULLS FIRST explicitly. It is one extra phrase and it eliminates a class of cross-dialect bug.

ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC NULLS LAST,  hire_date ASC NULLS LAST,  employee_id ASC) AS rn

The follow-up the interviewer always asks

After you write the query, expect one of these three probes. Each one tests whether the right function is picked. The answer maps the consumer's English back to the right tool.

•Probe

"What if two employees tie for third?"
"What if the report needs top 3 salary levels, not 3 employees?"
"What if salary is NULL for some rows?"

✓Your one-sentence answer

"Depends on the business rule. RANK if both ties should appear, ROW_NUMBER with a tiebreaker if exactly 3 are needed."
"Then DENSE_RANK is the right tool, because top 3 distinct salary levels can include any number of tied employees."
"I'd add NULLS LAST to the ORDER BY so missing salaries do not dominate the ranking, and ask whether NULL salaries should be excluded entirely."

When the interviewer hands you the question, do not jump straight to ROW_NUMBER. Pause, ask the tie-handling question, and let the answer pick the function. The pause is the move. Many candidates think the pause looks like indecision; it looks like deliberation.

Situation	Phrasing that flatlines	Phrasing that lands
The interviewer says 'top 3 per department'	"I'll ROW_NUMBER and filter rn <= 3."	"Before I write SQL, should ties at position N be included or excluded, and is the report counting distinct salary levels or distinct employees? That answer picks between ROW_NUMBER, RANK, and DENSE_RANK."
You realize you need a tiebreaker	"I'll add employee_id to break ties."	"Salary DESC primary, hire_date ASC because longer tenure wins on ties (business rule), employee_id ASC as the absolute fallback for determinism across parallel passes."
The interviewer asks 'what about NULLs'	"I'll filter them."	"NULLS LAST on the ORDER BY so missing salaries don't dominate a DESC sort. Whether to filter NULL rows entirely is a contract question for the report owner; both are valid depending on intent."
The interviewer says 'how does it scale'	"Window functions are O(N log N)."	"Composite index on (department_id, salary DESC, employee_id) so the engine walks the index in partition order without a residual sort. For warehouses, the equivalent is a clustering key; for a dashboard, materialize the top-N into a separate table and serve from that."
The interviewer says 'no window functions'	"Correlated subquery counting predecessors."	"Correlated subquery counting strict predecessors gives RANK semantics. To match ROW_NUMBER exactly, extend the inequality with an employee_id tiebreaker so ties resolve deterministically. On modern engines the optimizer rewrites it; on legacy engines it's O(N*D) per partition."

Deterministic Tiebreaks for Exactly N

Daily Life

Interviews

Solve top-N-per-group using a correlated subquery or self-join when the interviewer bans window functions.

At this level the interviewer's no-window fallback is less about whether you remember the syntax and more about whether you understand what the optimizer does with each form. Two fallbacks work. The correlated subquery is the cleaner write. The self-join is the form that lets you reason about the join algebra directly. Knowing when each one's plan diverges is the depth signal.

The correlated subquery fallback

	/* Top 3 per department, no window functions */
	SELECT
	department_id,
	employee_id,
	salary
	FROM employees AS e1
	WHERE (
	SELECT
	COUNT(*)
	FROM employees AS e2
	WHERE e2.department_id = e1.department_id
	AND e2.salary > e1.salary
	) < 3

The semantics: for each row e1, count how many rows in the same department strictly exceed its salary. If fewer than 3 rows beat it, e1 is in the top 3. Ties behave like RANK: if two employees share second place, neither has a strictly-greater predecessor, so both pass through the filter as ranks 2 and 2 (not 2 and 3). This is identical to RANK <= 3, not ROW_NUMBER <= 3. If the consumer wanted exactly N, this form is wrong without a tiebreaker condition.

Making the correlated subquery deterministic

To match ROW_NUMBER semantics (exactly N), extend the inequality to break ties on a unique column.

	/* Equivalent of ROW_NUMBER <= 3 without window functions */
	SELECT
	department_id,
	employee_id,
	salary
	FROM employees AS e1
	WHERE (
	SELECT
	COUNT(*)
	FROM employees AS e2
	WHERE e2.department_id = e1.department_id
	AND (
	e2.salary > e1.salary
	OR (
	e2.salary = e1.salary
	AND e2.employee_id < e1.employee_id
	)
	)
	) < 3

The OR clause makes the comparison strict: 'e2 beats e1' is now 'higher salary, or equal salary with lower employee_id.' Ties get broken by employee_id, which is unique. The query now matches ROW_NUMBER semantics exactly. Mentioning this conversion unprompted is the depth signal: it shows you understand that the choice between RANK and ROW_NUMBER is encoded in the subquery's tie-breaking condition, not in the surrounding SQL.

Plan implications

On a modern optimizer (Postgres 12+, Snowflake, BigQuery), the correlated subquery is rewritten into a hash-based set operation that runs in O(N + M) time. On older engines, the subquery runs once per outer row, giving O(N * D) where D is the average department size. For a 10-million-row employees table with 100 departments averaging 100k rows each, that is 10 million subquery executions on the bad plan. Mention this when the interviewer asks about scale: 'on engines that do not rewrite this into a hash semi-join, the correlated subquery is O(N * D); on those engines I would switch to the self-join form, which the optimizer plans more predictably.'

When the no-window question is really asking

'Now solve it without window functions' is rarely a real prohibition. It is usually one of two probes. First: do you understand the underlying algebra, or do you only remember the syntax? Showing that you can construct the answer from a correlated count proves the former. Second: can you reason about the optimizer's behavior on legacy systems? Naming the rewrite that modern engines do (and the rewrite older engines do not) proves you have shipped against both kinds of database. Pick the form that lets you answer both questions in one breath.

Why the interviewer asks the no-window version:

▸Tests whether you understand the underlying algebra, not just the syntax
▸Reveals which engines plan correlated subqueries efficiently
▸Surfaces whether you can fall back without panicking

•When the correlated subquery wins

The optimizer rewrites it cleanly into a hash semi-join
The output volume is small and the inner table fits in memory
Readability matters; the subquery reads as 'rows with fewer than 3 predecessors'
The interviewer wants to see the algebra, not the optimization

✓When the self-join wins

The optimizer rewrites the subquery poorly (legacy Postgres, older MySQL)
The plan is hard to predict and EXPLAIN shows nested-loop behavior
The query needs to expose join columns that the subquery cannot return
You want to manually control the join order with hints

Exactly-N vs At-Least-N Semantics

Daily Life

Interviews

Discuss index strategies, partition pruning, and how to handle "top N with ties" at warehouse scale.

The closing escalation is about scale. The interviewer wants to know whether you can support this query at production volume. The right answer covers three layers: the supporting index, the partition or cluster layout, and the materialization strategy when the query powers a dashboard. Hitting all three is what flips this question from a basic SQL screen to a verdict the interviewer remembers at the debrief.

The supporting index

ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) requires the engine to sort each department's rows by salary. An index on (department_id, salary DESC) lets the engine walk the index in partition + order without a sort step. Without that index, the engine has to scan the entire employees table and sort it in memory per partition (or spill to disk on large tables). The cost difference is several orders of magnitude.

	CREATE INDEX idx_emp_dept_salary
	ON employees(department_id, salary DESC, employee_id) ;

Including the tiebreaker column (employee_id) is the move most candidates miss. Without it, the engine has to sort within each (department, salary) tie to apply the tiebreaker. With it, the index walk is sufficient. State the included column unprompted: 'I would include the tiebreaker column in the index so the entire ORDER BY is satisfied without a residual sort.' That sentence converts an index recommendation into a production-grade index design.

At Netflix in 2022, a 'top 5 most-watched titles per country per week' dashboard was rebuilt after a quarter of complaints that 'Canada and Switzerland kept showing different results between refreshes.' The original query used ROW_NUMBER OVER (PARTITION BY country ORDER BY view_hours DESC) with no tiebreaker; titles with identical view counts in the long tail of small markets reshuffled non-deterministically across the parallel passes the warehouse engine made. The fix was a three-column ORDER BY: view_hours DESC, release_date DESC, title_id ASC. The behavior locked. The runbook entry now reads 'any ROW_NUMBER that powers a dashboard must include a unique tiebreaker column, and the supporting clustering key must include that column too.' That sentence, almost word for word, has shown up as the rubric for the Netflix data engineering SQL screen for the two years since.

Partition or cluster layout for warehouses

On Snowflake, BigQuery, or Redshift, indexes are not the right primitive. The equivalent is the clustering or partitioning key. If the dashboard always queries by department, cluster the employees table on department_id; the engine will prune partitions and execute the window function in parallel across departments. If the dashboard queries by date range and you compute top-N per (department, month), the clustering should match that two-column pattern. Naming the warehouse-equivalent of the index is what separates someone who has tuned OLTP queries from someone who has tuned analytical queries.

Materialization for dashboards

If the query powers a dashboard that refreshes every five minutes, recomputing the top N from a billion-row source on every refresh is wasteful. The right answer is to materialize the top-N result into a separate, much smaller table, refresh that table on the underlying data's cadence (usually nightly), and have the dashboard query the materialized table. State the materialization beat unprompted: 'for a dashboard that refreshes more frequently than the underlying data changes, I would materialize the top-N result into a separate table and serve from that.' This is the architectural move that converts the question from 'write the query' to 'design the report.'

Why this matters at the production level

Top N per group queries scale poorly without help. A naive query on a billion-row employees table with no supporting index spends most of its runtime sorting partitions in memory. The supporting index converts the sort into an index walk. The cluster key extends the same idea to warehouses. The materialization step removes the per-refresh computation entirely. Each layer cuts the cost by an order of magnitude. The candidate who names all three has demonstrated that they have shipped this query against real production constraints and know which lever to pull when.

•Answer that stops at the syntax

Writes the correct ROW_NUMBER query
Defends it on a clean toy dataset
Has no answer when asked about a billion rows
Treats performance as somebody else's problem

✓Answer that owns the production path

Names the supporting index unprompted, including the tiebreaker column
Translates the index to a cluster key on warehouse engines
Recommends materialization when the refresh cadence exceeds the data cadence
Treats the query as the surface of a designed report, not as the report itself

The closing summary

Close with a four-sentence wrap. 'I used ROW_NUMBER with a deterministic tiebreaker (hire_date, then employee_id) so the same row wins every refresh. The tie-handling choice between ROW_NUMBER, RANK, and DENSE_RANK is driven by the consumer's question, not by the syntax. NULLS LAST in the ORDER BY prevents missing salaries from dominating the ranking. For scale, a composite index on (department_id, salary DESC, employee_id), and a materialized top-N table when this powers a dashboard.' Four sentences. Pattern, semantics, NULL safety, scale. The shape generalizes to every variant of this question.

❯❯❯PUTTING IT ALL TOGETHER

> You are in a Stripe data engineering interview. The interviewer asks: 'Top 3 highest-paid employees per department, but the bonus pool requires the result to be deterministic and to honor a business rule about ties.'

You ask first: 'Should ties at position N be included, or do I break them? And what is the tiebreaker rule?' The interviewer says: 'Include all employees in the top 3 positions, with ties resolved in favor of tenure if you must pick.'

You say: 'Then RANK is the right tool. Ties share the rank, so multiple employees at second place all appear. I will add hire_date and employee_id to the ORDER BY for determinism when ties get broken.'

You write the CTE: RANK() OVER (PARTITION BY department_id ORDER BY salary DESC NULLS LAST, hire_date ASC, employee_id ASC). Outer filter: WHERE rank <= 3.

Follow-up: 'What if salary is NULL for some rows?' You say: 'NULLS LAST in the ORDER BY prevents missing salaries from dominating. If NULL salary should be excluded entirely, I filter it in the CTE before ranking.'

Closing: 'For scale, a composite index on (department_id, salary DESC, hire_date, employee_id) so the index walk satisfies the full ORDER BY without a residual sort.'

KEY TAKEAWAYS

Ask the tie question before writing SQL, because the answer picks the function: RANK to include ties at position N, ROW_NUMBER plus a tiebreaker for exactly N, DENSE_RANK for the top N distinct values with all matching rows.

ROW_NUMBER with no deterministic tiebreaker is a non-reproducible bug; order by the metric, then a business tiebreaker, then a guaranteed-unique column, as in salary DESC, hire_date ASC, employee_id ASC.

The tiebreaker column order is a business rule in disguise: hire_date ASC means longer tenure wins on a tie, and that choice belongs to the consumer, not to the query author.

Write NULLS LAST explicitly, because Postgres, MySQL, and SQL Server disagree on where NULLs land and a NULL salary can otherwise sit at the top of the ranking.

The no-window fallback (SELECT COUNT(*) FROM employees e2 WHERE e2.department_id = e1.department_id AND e2.salary > e1.salary) < 3 has RANK semantics, not ROW_NUMBER; adding an OR on a unique column to the inequality converts it to exactly N.

Scale comes in three layers: a composite index on (department_id, salary DESC, employee_id) including the tiebreaker so no residual sort remains, a matching cluster key on warehouses where indexes do not exist, and a materialized top-N table when the dashboard refreshes faster than the source changes.

The single most common SQL interview question, hiding in plain sight

Category: SQL
Difficulty: intermediate
Duration: 25 minutes
Challenges: 0 hands-on challenges

Topics covered: The ROW_NUMBER + Filter Trick, Ties, NULLs, and the RANK Trap, ROW_NUMBER vs RANK vs DENSE_RANK, Deterministic Tiebreaks for Exactly N, Exactly-N vs At-Least-N Semantics

Lesson Sections

The ROW_NUMBER + Filter Trick (concepts: sqlRankDenseRank)
The shape of the question rarely changes. 'Top 3 highest-paid employees per department.' 'Top 5 best-selling products per category.' 'Top 10 most-recent orders per customer.' What changes is what 'top 3' actually means when the data does not cooperate. Two employees tied for second place. A category with only two products. A NULL salary on a row that should be ignored. Every one of those edge cases changes the right query. The interviewer at this level is watching whether you anticipate the edge
Ties, NULLs, and the RANK Trap (concepts: sqlRowNumber)
The canonical solution still applies. ROW_NUMBER inside a CTE, filtered to rn <= N in the outer query. The shape is unchanged. What changes at this level is how you talk about the tiebreaker. ROW_NUMBER without a deterministic tiebreaker returns arbitrary results on ties, and the interviewer will probe that. The query with the tiebreaker in place Why the multi-column ORDER BY matters Without a tiebreaker, ROW_NUMBER assigns positions arbitrarily for tied salaries. The same query on the same data
ROW_NUMBER vs RANK vs DENSE_RANK (concepts: sqlRankDenseRank)
This section is where the question stops being about syntax and starts being about which tool to reach for. ROW_NUMBER, RANK, and DENSE_RANK look interchangeable. They are not. Each one answers a different business question, and picking the wrong one produces visibly plausible numbers that disagree with what the consumer asked for. The candidate at this level is being scored on whether they pick the right function from the answer to the clarifying question, not on whether they can recite what ea
Deterministic Tiebreaks for Exactly N (concepts: sqlSubqueryCorrelated)
At this level the interviewer's no-window fallback is less about whether you remember the syntax and more about whether you understand what the optimizer does with each form. Two fallbacks work. The correlated subquery is the cleaner write. The self-join is the form that lets you reason about the join algebra directly. Knowing when each one's plan diverges is the depth signal. The correlated subquery fallback The semantics: for each row e1, count how many rows in the same department strictly exc
Exactly-N vs At-Least-N Semantics (concepts: sqlRowNumber)
The closing escalation is about scale. The interviewer wants to know whether you can support this query at production volume. The right answer covers three layers: the supporting index, the partition or cluster layout, and the materialization strategy when the query powers a dashboard. Hitting all three is what flips this question from a basic SQL screen to a verdict the interviewer remembers at the debrief. The supporting index ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC)