SQL Window Function Interview Questions

Window-function questions test whether you know when to keep row-level detail while adding a calculation across related rows. Unlike GROUP BY, window functions do not collapse the result into one row per group.

The key vocabulary is OVER, PARTITION BY, and ORDER BY. Partitioning defines the group of rows the function can see; ordering defines sequence inside that group.

Common window-function interview prompts

Answer each one yourself before you look: write your query or your reasoning in the box, or open the playground and run it. Then reveal the model answer and compare. Nothing you type is saved or sent anywhere.

1. Return the highest-priced product in each category.

   Your answer Try it in the playground →
   Reveal a model answer

   with ranked as (
     select c.category_name,
            p.product_name,
            p.price,
            row_number() over (
              partition by p.category_id
              order by p.price desc, p.product_name
            ) as rn
     from products as p
     join categories as c on c.category_id = p.category_id
   )
   select category_name, product_name, price
   from ranked
   where rn = 1
   order by category_name

   Number the rows inside each category, then filter in an outer query, because WHERE runs before the window exists. In DuckDB, QUALIFY rn = 1 saves the CTE. Ask whether a tie for top price should return both products; if so, use rank().

2. Rank movies within each genre by rating.

   Your answer Try it in the playground →
   Reveal a model answer

   select genre,
          title,
          rating,
          rank() over (partition by genre order by rating desc) as genre_rank
   from movies
   order by genre, genre_rank, title

   PARTITION BY restarts the ranking per genre and every movie keeps its row. rank() lets two equally rated movies share a place; say which tie behavior you chose and why.

3. Calculate monthly revenue and a running total.

   Your answer Try it in the playground →
   Reveal a model answer

   with monthly as (
     select date_trunc('month', o.order_date) as order_month,
            sum(oi.quantity * oi.unit_price) as revenue
     from orders as o
     join order_items as oi on oi.order_id = o.order_id
     group by order_month
   )
   select order_month,
          revenue,
          sum(revenue) over (
            order by order_month
            rows between unbounded preceding and current row
          ) as running_revenue
   from monthly
   order by order_month

   Aggregate to one row per month first, then window over the months. Name the ROWS frame: it is the default's behavior here, but it states the intent and survives a sort key that ties.

4. Compare each session duration with the previous session for the same channel.

   Your answer Try it in the playground →
   Reveal a model answer

   select channel,
          session_id,
          session_date,
          duration_seconds,
          lag(duration_seconds) over (
            partition by channel
            order by session_date, session_id
          ) as previous_duration
   from sessions
   order by channel, session_date, session_id

   lag() reads the prior row in each channel's sequence. The first session in a channel has no predecessor, so its previous_duration is NULL; say whether that should stay NULL or become 0.

5. Explain the difference between ROW_NUMBER, RANK, and DENSE_RANK.

   Your answer
   Reveal a model answer

   All three number rows in order. On a tie, row_number() still gives distinct numbers (1, 2, 3), rank() shares the number and skips (1, 1, 3), and dense_rank() shares it without a gap (1, 1, 2). Pick by what 'top 3' should mean when two rows tie.

with products_with_categories as (
  select p.product_name,
         c.category_name,
         p.price
  from products as p
  join categories as c on c.category_id = p.category_id
)
select product_name,
       category_name,
       price,
       row_number() over (
         partition by category_name
         order by price desc, product_name
       ) as price_rank
from products_with_categories

What matters

The classic top-row-per-group question is a two-step query. First, calculate a row number inside each group. Then filter the numbered rows in an outer query or CTE. You usually cannot filter the window function directly in WHERE because, in logical order, WHERE is evaluated before window functions.

For running totals, the frame matters. The frame rows between unbounded preceding and current row means "start at the first row in this order and add through the current row." Without a clear order, a running total is not meaningful.

with monthly_revenue as (
  select date_trunc('month', o.order_date) as order_month,
         sum(oi.quantity * oi.unit_price) as revenue
  from orders as o
  join order_items as oi on oi.order_id = o.order_id
  group by 1
)
select order_month,
       revenue,
       sum(revenue) over (
         order by order_month
         rows between unbounded preceding and current row
       ) as running_revenue
from monthly_revenue
order by order_month

How to talk it through

Say what needs to stay visible: "I need to keep one product row, but rank products within each category. I will partition by category, order by price descending, then filter to row_number equals 1 in an outer query."

If ties matter, ask before choosing the ranking function. ROW_NUMBER picks one row per group, RANK can return multiple tied rows and leave gaps, and DENSE_RANK can return tied rows without gaps.

Practice next

Work through the window functions lesson, keep the window function reference open, and review why you cannot filter a window function in WHERE. For compact syntax review, use the window functions cheatsheet.