Interview Q&A / SQL & DBMS

1. 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
2. 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
3. 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
4. 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
5. 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
6. 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