Query Dojo

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

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

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