Skip to content
RED SAGSkills
mediumData Analyst interview question

What are SQL window functions and when have you used them?

What the interviewer wants to hear

  • Compute across related rows without collapsing them like GROUP BY
  • PARTITION BY and ORDER BY inside OVER
  • Ranking: ROW_NUMBER, RANK, DENSE_RANK
  • Running totals and moving averages
  • LAG and LEAD for comparing with previous rows

Model answer

Window functions calculate a value across a set of related rows while still keeping each row, unlike GROUP BY which collapses rows. The OVER clause defines the window, with PARTITION BY to split groups and ORDER BY to set order. I use ROW_NUMBER to get the latest record per customer, for example to remove duplicates or find each user's first purchase. I use RANK or DENSE_RANK for top N products per category, choosing based on how ties should be handled. For trends, SUM OVER with ORDER BY gives running totals, and AVG over the last 7 rows gives a 7-day moving average. LAG and LEAD let me compare each month with the previous month to compute month-over-month growth without a self join. They make queries shorter and usually faster than equivalent subqueries.

Can you answer it under time pressure?

Take a timed medium round and get scored on every answer.

Start mock interview

More medium Data Analyst questions