Interview Q&A / SQL & DBMS

1. Write an SQL query to find the second highest salary.

   Find the highest salary that is less than the maximum, with SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). For the Nth highest, use DENSE_RANK() so that duplicate salaries share a rank, or ORDER BY DISTINCT salary with LIMIT and OFFSET.

   BeginnerIn-depthCoding3 min read
2. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

   All three number rows in a given order. ROW_NUMBER always gives unique numbers 1, 2, 3, even for ties. RANK gives tied rows the same number and then skips ahead (1, 2, 2, 4). DENSE_RANK gives tied rows the same number without skipping (1, 2, 2, 3).

   IntermediateIn-depth4 min read
3. How is a window function different from GROUP BY?

   GROUP BY collapses rows into one row per group. A window function computes an aggregate or rank over a set of related rows but keeps every original row in the output.

   BeginnerQuick answer1 min read
4. What do LAG and LEAD do in SQL?

   LAG returns a value from a previous row and LEAD from a following row, within the window order. They compare a row to its neighbour without a self join.

   IntermediateQuick answer1 min read
5. What do PARTITION BY and ORDER BY do inside OVER()?

   PARTITION BY splits rows into independent groups, and ORDER BY sorts rows within each group. For aggregates, adding ORDER BY also turns the result into a running calculation.

   IntermediateQuick answer1 min read
6. What does NTILE do and where is it used?

   NTILE(n) divides the ordered rows into n roughly equal buckets and returns the bucket number for each row. It is used for quartiles, percentiles and splitting data into groups.

   IntermediateQuick answer1 min read
7. Write a query to calculate a running total of daily sales.

   Use SUM(amount) OVER (ORDER BY day) to get a cumulative sum. Add PARTITION BY to restart the total per group, and use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to be explicit.

   IntermediateQuick answerCoding1 min read
8. Write a query to find the highest-paid employee in each department.

   Compute MAX(salary) per department in a subquery and join back, or use RANK() OVER (PARTITION BY dept ORDER BY salary DESC) and keep rank 1. Both return all tied employees.

   IntermediateQuick answerCoding1 min read
9. What is the default window frame, and why can LAST_VALUE give a surprising answer?

   With ORDER BY, the default frame runs from the first row to the current row, so LAST_VALUE returns the current row's value. To get the true last value, extend the frame with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

   AdvancedQuick answer1 min read