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, andORDER BY. Partitioning defines the group of rows the function can see; ordering defines sequence inside that group.

Common window-function interview prompts

1. Return the highest-priced product in each category.
2. Rank movies within each genre by rating.
3. Calculate monthly revenue and a running total.
4. Compare each session duration with the previous session for the same channel.
5. Explain the difference between ROW_NUMBER,RANK, and DENSE_RANK.

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 inWHERE because WHERE runs before the window calculation.

For running totals, the frame matters. The framerows 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 referenceopen, and review why you cannot filter a window function in WHERE. For compact syntax review, use thewindow functions cheatsheet.