Find the top-N records per group and a running total per group in SQL.

TL;DR: Use a ranking window function partitioned by the group and ordered by the metric, then filter to rank <= N. Pick the function by how you want ties handled: ROW_NUMBER gives exactly N (arbitrary tiebreak), RANK keeps ties and skips numbers, DENSE_RANK keeps ties without skipping. For a running total, SUM with an ordered frame (ROWS UNBOUNDED PRECEDING). Filtering a window result requires a subquery or CTE, since you cannot use a window function in WHERE.

How to approach it. State that this is a window-function problem, and that the key decision is which ranking function based on tie semantics. Note the structural rule: window functions cannot appear in WHERE, so you rank in a CTE/subquery and filter outside it.

A strong answer. Top-N per group:

WITH ranked AS (
  SELECT
    category,
    product,
    sales,
    ROW_NUMBER() OVER (
      PARTITION BY category ORDER BY sales DESC
    ) AS rn          -- exactly one row per rank; arbitrary among ties
  FROM products
)
SELECT category, product, sales
FROM ranked
WHERE rn <= 3;        -- top 3 per category

The choice of ranking function is the real test. Given tied values at the boundary, the three functions diverge:

ROW_NUMBER breaks ties arbitrarily unless you add a deterministic tiebreaker to ORDER BY, so two runs can return different rows. RANK and DENSE_RANK keep tied rows together; the difference is whether they skip the next number after a tie.

Running total per group uses an ordered frame:

SELECT
  category, product, sale_date, sales,
  SUM(sales) OVER (
    PARTITION BY category ORDER BY sale_date
    ROWS UNBOUNDED PRECEDING        -- running total within category, by date
  ) AS running_total
FROM products;

ROWS UNBOUNDED PRECEDING (to current row) defines the cumulative frame; omit it and the default frame (RANGE UNBOUNDED PRECEDING) can behave differently with tied ORDER BY values, a subtle bug worth naming.

Key takeaways

• Window functions are evaluated after WHERE, so rank in a CTE and filter the rank outside it; this is structural, not stylistic.
• Pick the ranking function from the tie requirement: ROW_NUMBER for exactly N, RANK for ties-included, DENSE_RANK for distinct values.
• A running total needs an explicit ROWS frame; the default RANGE frame lumps tied ORDER BY rows together and inflates the cumulative value.
• Always add a deterministic tiebreaker to ORDER BY, otherwise ROW_NUMBER results are non-reproducible.

What interviewers probe next.

• "ROW_NUMBER vs RANK vs DENSE_RANK on ties?" Exactly the distinction above; pick by whether you want exactly N, ties-included, or distinct-values.
• "Why the subquery/CTE?" Window functions are computed after WHERE, so you cannot filter on rn in the same query level; rank in a CTE, filter outside.
• "ROWS vs RANGE frame?" ROWS counts physical rows; RANGE groups peer rows with equal ORDER BY values, which changes running totals when dates tie.
• "Do this efficiently at scale?" Ensure the partition/order columns are indexed (or the table is partitioned/clustered on them) so the engine avoids a full sort.

Common mistakes.

• Using RANK when the requirement is exactly N (ties return extra rows) or ROW_NUMBER when ties should be kept.
• Trying to filter a window function in WHERE instead of a CTE/subquery.
• Forgetting the frame clause on a running total and getting RANGE behavior on tied keys.
• No deterministic tiebreaker in ORDER BY, so ROW_NUMBER results vary run to run.