SQL Window Functions Practice Questions: 8 With Answers

Window functions become interview-ready only when you can choose the right window, grain, order, and frame without being told which function to use. These eight SQL window function practice questions turn those decisions into a progressive drill. Every solution runs against the same small sales table, and every output below is checked in both SQLite and PostgreSQL before publication.

If the syntax is still unfamiliar, read the six core window-function patterns first. If you already recognize OVER, PARTITION BY, and window ORDER BY, stop before each solution and start with Exercise 1.

What this practice set covers

• Latest row per group with a deterministic ROW_NUMBER().
• Ranking ties and top score levels with RANK() and DENSE_RANK().
• Period-over-period comparisons with LAG().
• Running totals and moving averages with explicit ROWS frames.
• Percent of total without collapsing detail rows.
• Aggregating first, then ranking the grouped result.

The dataset and its grain

The table contains one row per representative per month. month uses the sortable YYYY-MM format, so text ordering is chronological for this dataset.

Before writing each query, say what one output row should represent. That sentence is your grain check. Then identify whether rows should be divided by representative, region, or month, and whether sequence matters inside each division.

Exercise 1: return the latest month for every representative

Prompt: Return one row per representative containing their most recent month and revenue. Use the stated table grain: one row per representative per month.

Expected columns: rep, month, revenue.

Hint: Number rows inside each representative's history, put the newest month first, and filter to row 1 in an outer query.

WITH numbered AS (
  SELECT rep,
         month,
         revenue,
         ROW_NUMBER() OVER (
           PARTITION BY rep
           ORDER BY month DESC
         ) AS row_num
  FROM rep_monthly_sales
)
SELECT rep,
       month,
       revenue
FROM numbered
WHERE row_num = 1
ORDER BY rep;

The inner query calculates the row number; the outer query filters it. Standard SQL does not allow a window result in the same query block's WHERE clause because window calculations happen later. BigQuery and Snowflake offer QUALIFY, but the outer-query form is the portable interview answer.

Exercise 2: show both competition rank and dense rank

Prompt: For March, rank representatives within each region by revenue. Show both competition rank, which leaves a gap after a tie, and dense rank, which does not.

Expected columns: region, rep, revenue, revenue_rank, revenue_level.

Hint: A tie must remain a peer group, so do not add rep to the ranking window's ORDER BY. Use the final ORDER BY only to make display order stable.

SELECT region,
       rep,
       revenue,
       RANK() OVER (
         PARTITION BY region
         ORDER BY revenue DESC
       ) AS revenue_rank,
       DENSE_RANK() OVER (
         PARTITION BY region
         ORDER BY revenue DESC
       ) AS revenue_level
FROM rep_monthly_sales
WHERE month = '2026-03'
ORDER BY region, revenue DESC, rep;

Cy and Di are peers because their revenue values tie. RANK() gives Eve position 3 because two rows come before her. DENSE_RANK() gives Eve level 2 because only one distinct revenue value is higher. The deeper ROW_NUMBER vs RANK vs DENSE_RANK guide covers cutoff semantics in detail.

Exercise 3: return the top two revenue levels per region

Prompt: For March, return everyone whose revenue belongs to the top two distinct revenue levels in their region. Keep all ties at the cutoff.

Expected columns: region, rep, revenue, revenue_level.

Hint: “Two levels” describes DENSE_RANK() <= 2, not two physical rows.

WITH ranked AS (
  SELECT region,
         rep,
         revenue,
         DENSE_RANK() OVER (
           PARTITION BY region
           ORDER BY revenue DESC
         ) AS revenue_level
  FROM rep_monthly_sales
  WHERE month = '2026-03'
)
SELECT region,
       rep,
       revenue,
       revenue_level
FROM ranked
WHERE revenue_level <= 2
ORDER BY region, revenue DESC, rep;

This returns five rows, not four. A fixed row limit and a score-level cutoff are different business rules. Ask which rule the interviewer means before choosing ROW_NUMBER() or DENSE_RANK().

Exercise 4: calculate month-over-month revenue change

Prompt: For Ava, show each month, its revenue, the previous month's revenue, and the absolute change.

Expected columns: month, revenue, previous_revenue, revenue_change.

Hint: LAG(value) reads a value from an earlier row in the window order. The first row has no predecessor.

WITH compared AS (
  SELECT month,
         revenue,
         LAG(revenue) OVER (ORDER BY month) AS previous_revenue
  FROM rep_monthly_sales
  WHERE rep = 'Ava'
)
SELECT month,
       revenue,
       previous_revenue,
       revenue - previous_revenue AS revenue_change
FROM compared
ORDER BY month;

The first change is NULL, not zero: there is no earlier month to compare. In a real table, verify that every expected month exists. LAG() compares adjacent rows, so a missing February would make March compare directly with January unless you build a complete calendar first.

Exercise 5: calculate a running total

Prompt: For Ben, show cumulative revenue from the first available month through the current month.

Expected columns: month, revenue, running_revenue.

Hint: Use an aggregate as a window function and state the frame explicitly.

SELECT month,
       revenue,
       SUM(revenue) OVER (
         ORDER BY month
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue
FROM rep_monthly_sales
WHERE rep = 'Ben'
ORDER BY month;

Writing the frame makes the intended row-by-row accumulation visible. Relying on the default frame can behave unexpectedly when the window order contains peers. The current PostgreSQL window-function documentation explains why the default frame extends through the current row's last peer.

Exercise 6: calculate a two-month moving average

Prompt: For Cy, show revenue and the average across the current row and the one preceding row.

Expected columns: month, revenue, average_2_months.

Hint: A two-row moving window is ROWS BETWEEN 1 PRECEDING AND CURRENT ROW.

SELECT month,
       revenue,
       ROUND(
         AVG(revenue) OVER (
           ORDER BY month
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
         ),
         1
       ) AS average_2_months
FROM rep_monthly_sales
WHERE rep = 'Cy'
ORDER BY month;

January averages one row because no preceding row exists. Also notice that this is a two-row average, not automatically a two-calendar-month average. Gaps in the data change that meaning.

Exercise 7: calculate each representative's share of regional revenue

Prompt: For March, keep one row per representative and calculate their percentage of regional revenue.

Expected columns: region, rep, revenue, percent_of_region.

Hint: The denominator must be repeated on every detail row, so calculate the regional total with SUM() OVER (PARTITION BY region).

SELECT region,
       rep,
       revenue,
       ROUND(
         100.0 * revenue / SUM(revenue) OVER (PARTITION BY region),
         1
       ) AS percent_of_region
FROM rep_monthly_sales
WHERE month = '2026-03'
ORDER BY region, revenue DESC, rep;

There is no window ORDER BY because the denominator is the whole regional partition. Adding one would turn the denominator into a running total. If that distinction is unclear, revisit GROUP BY vs window functions: this result preserves representatives instead of collapsing them into regional rows.

Exercise 8: aggregate regions first, then rank them

Prompt: Sum all three months of revenue by region, then rank regions from highest to lowest total.

Expected columns: region, total_revenue, revenue_rank.

Hint: The rank should operate on one row per region, so establish that grain in a CTE before applying the window.

WITH region_totals AS (
  SELECT region,
         SUM(revenue) AS total_revenue
  FROM rep_monthly_sales
  GROUP BY region
)
SELECT region,
       total_revenue,
       DENSE_RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM region_totals
ORDER BY revenue_rank, region;

Window functions run after grouping and aggregation. That is why a grouped total can be the input to a rank in the next query stage. It is also why trying to aggregate a window result in the same stage usually signals that the query grain has not been separated clearly enough.

A five-step interview checklist

Use this sequence before typing:

1. State the output grain. One row per representative, representative-month, region, or rank level?
2. Choose the partition. Which rows should be calculated independently?
3. Choose the window order. What makes a row previous, next, first, or highest?
4. Choose tie and frame behavior. Fixed rows, shared ranks, distinct levels, or a moving range?
5. Filter at the correct stage. Use an outer query for window results, or QUALIFY only when the stated dialect supports it.

The PostgreSQL expression reference documents the standard placement and frame rules. BigQuery and Snowflake document their QUALIFY extensions. In an interview, say which dialect you are using before relying on an extension.

Turn the exercises into a timed practice session

First, solve Exercises 1 through 3 without looking at the answers. Then give yourself twelve minutes for Exercises 4 through 8. For every answer, explain the output grain, partition, order, and frame aloud. Correct syntax with an unclear business rule is still a weak analyst answer.

When you are ready to test more than SQL syntax, take the free data analyst readiness diagnostic. It checks SQL interpretation, metric reasoning, and visualization judgment without requiring an account. For more coding repetitions, use the SQL window functions practice hub, where the questions run directly in your browser with instant grading.