QuickDevGuide

Home › SQL Database

Classic SQL Interview Queries — SQL Database

Patterns that come up again and again in interviews and real reporting work. Question Technique Nth highest salary DENSERANK() or ORDER BY … LIMIT 1 OFFSET n-1 Top N per group ROWNUMBER() OVER (PARTITION BY …) in a CTE Find duplicates GROUP BY … HAVING COUNT() > 1 Delete duplicates, keep one ROWNUMBER() / MIN(id) per group Customers with no orders LEFT JOIN … WHERE o.id IS NULL or NOT EXISTS Employees earning more than their manager self join Consecutive days / gaps date minus ROWNUMBER() ("gaps and islands") Cumulative sum SUM() OVER (ORDER BY …)

Key Takeaways