SQL CTE Interview Questions

Common table expressions (WITH clauses) make long queries readable by naming each step. Recursive CTEs go further and walk hierarchies or generate sequences.

Interviewers like CTEs because they show how you structure a solution. These questions practice both everyday and recursive CTEs.

Patterns to know

  • Breaking a hard query into named steps with WITH
  • Recursive CTEs for org charts and other hierarchies
  • Generating a sequence to find missing values
  • Combining CTEs with window functions for top N per group

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 Compare to the Previous Row With LAG Compare Sales to Previous Day Premium
  5. Medium Find Missing IDs in a Sequence Detect Missing Order IDs Premium
  6. Medium 90th Percentile Salary Calculate 90th Percentile Salary Free
  7. Medium 7-Day Moving Average 7-Day Moving Average of Sales Premium
  8. Medium Cumulative Sum Over Time Cumulative Student Enrollment Growth Premium
  9. Medium Select Every Other Row Select Every Other Row Premium
  10. Medium Rows Above Their Group Average Above-Average Track Length per Album Premium
  11. Medium Best Seller per Category Best Seller in Each Category Premium
  12. Medium Nth Highest Salary Find the Nth Highest Salary Free
  13. Medium Highest Salary in Each Department Find the Highest Salary in Each Department Free
  14. Medium Employees With the Same Salary Find Employees Who Share a Salary Free
  15. Medium Second Highest Salary per Department Second Highest Salary in Each Department Free
  16. Medium Group By Month Monthly Orders and Revenue Free
  17. Medium Subquery vs CTE Courses Above the Average Enrollment Free
  18. Medium Department With the Most Employees Departments With the Most Recent Hires Free

Hard questions

  1. Hard Monthly User Retention Rate Calculate Monthly Retention Rate Premium
  2. Hard Year-over-Year Revenue Growth Year-Over-Year Revenue Growth Analysis Premium
  3. Hard Median Without a MEDIAN Function Department Salary Median (No MEDIAN Function) Premium
  4. Hard Org Chart Hierarchy With a Recursive CTE Full Organizational Hierarchy Depth Premium
  5. Hard Longest Winning Streak Longest Winning Streak in Games Premium
  6. Hard User Session Duration Calculate User Session Duration Premium
  7. Hard Cohort Retention Analysis Monthly Signup Cohort Retention Premium
  8. Hard Cumulative Distribution Cumulative Salary Distribution Premium
  9. Hard Consecutive Increases (Gaps and Islands) Detect Consecutive Price Increases Premium
  10. Hard Fibonacci With a Recursive CTE Generate Fibonacci Sequence with SQL Premium

More SQL interview topics