Query Dojo

SQL Subquery Interview Questions

Subqueries let one query use the result of another. Interviewers use them to check that you can break a problem into steps, like finding the average first and then the rows above it.

Several questions here can also be solved with joins or window functions. Comparing approaches is a good way to prepare for follow-up questions.

Patterns to know

  • Comparing each row to an overall value, such as the average
  • Second highest value and other single-value lookups
  • IN vs EXISTS, and the NULL trap in NOT IN
  • Correlated subqueries that compare a row to its own group
  • Subqueries in FROM to aggregate an aggregate

Easy questions

  1. Easy Second Highest Salary Find the Runner-Up Salary Free
  2. Easy Rows Above the Average Students Exceeding Average GPA Free
  3. 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 EXISTS vs IN Find Customers With Recent Orders 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 Compound Annual Growth Rate (CAGR) Calculate CAGR (Compound Annual Growth Rate) Premium
  7. Hard User Session Duration Calculate User Session Duration Premium
  8. Hard Cohort Retention Analysis Monthly Signup Cohort Retention Premium
  9. Hard Cumulative Distribution Cumulative Salary Distribution Premium
  10. Hard Consecutive Increases (Gaps and Islands) Detect Consecutive Price Increases Premium
  11. Hard Business Metrics in One Query Multi-Metric Executive Dashboard Premium
  12. 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 CTE Interview QuestionsSQL SELECT and Filtering Interview QuestionsSQL INSERT, UPDATE and DELETE Interview QuestionsSQL Transaction Interview Questions