Query Dojo

SQL Window Function Interview Questions

Window functions are where most SQL interviews separate candidates. They compute a value for every row while still seeing the rows around it, so you can rank, compare and accumulate without collapsing your result the way GROUP BY does.

The questions below cover the patterns that come up again and again. Each one runs on a real database in your browser, and your result is checked against the expected output.

Patterns to know

  • Top N per group with ROW_NUMBER, RANK or DENSE_RANK and PARTITION BY
  • Comparing a row to the previous or next one with LAG and LEAD
  • Running totals and cumulative sums with SUM() OVER (ORDER BY ...)
  • Moving averages with a frame like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  • Percentiles and buckets with NTILE, PERCENT_RANK and CUME_DIST
  • Gaps and islands: streaks of consecutive days

Easy question

  1. Easy Longest Item per Group Each Artist's Longest Song Free

Medium questions

  1. Medium Top 3 Salaries per Department Identify Top 3 Earners per Department Premium
  2. Medium Nth Purchase per Customer Find Customer's Third Purchase Premium
  3. Medium Longest Consecutive Login Streak Daily Active User Streaks Premium
  4. Medium Running Total Daily Running Total of Sales Premium
  5. Medium RANK vs DENSE_RANK Product Rankings with Tie Handling Premium
  6. Medium Compare to the Previous Row With LAG Compare Sales to Previous Day Premium
  7. Medium 90th Percentile Salary Calculate 90th Percentile Salary Free
  8. Medium 7-Day Moving Average 7-Day Moving Average of Sales Premium
  9. Medium Cumulative Sum Over Time Cumulative Student Enrollment Growth Premium
  10. Medium Select Every Other Row Select Every Other Row Premium
  11. Medium Best Seller per Category Best Seller in Each Category Premium
  12. Medium Salary vs Department Average Compare Employee Salary to Department Average Premium
  13. Medium Ranking With Ties Student GPA Ranking with Ties Premium

Hard questions

  1. Hard Year-over-Year Revenue Growth Year-Over-Year Revenue Growth Analysis Premium
  2. Hard Median Without a MEDIAN Function Department Salary Median (No MEDIAN Function) Premium
  3. Hard Longest Winning Streak Longest Winning Streak in Games Premium
  4. Hard User Session Duration Calculate User Session Duration Premium
  5. Hard Consecutive Increases (Gaps and Islands) Detect Consecutive Price Increases Premium

More SQL interview topics

SQL JOIN Interview QuestionsSQL GROUP BY Interview QuestionsSQL Subquery Interview QuestionsSQL CTE Interview QuestionsSQL SELECT and Filtering Interview QuestionsSQL INSERT, UPDATE and DELETE Interview QuestionsSQL Transaction Interview Questions