Top SQL & Relational Database Interview Questions & Answers

Question 1

What are SQL Window Functions and how do they differ from GROUP BY?

Theoretical Concept

Window functions perform calculations across a set of table rows related to the current row without collapsing the rows.

Comprehensive Answer

While a GROUP BY clause aggregates rows and collapses them into a single summary row per group, Window Functions (using the OVER clause with PARTITION BY and ORDER BY) compute aggregate or ranking values while preserving the original row-level identity. This makes them indispensable for running totals, moving averages, and finding the Nth highest value.

Code Demonstration

Database

-- Finding the top 2 highest scoring students in each course
WITH RankedStudents AS (
    SELECT 
        student_name,
        course_name,
        score,
        DENSE_RANK() OVER (PARTITION BY course_name ORDER BY score DESC) as rank_pos
    FROM student_evaluations
)
SELECT student_name, course_name, score
FROM RankedStudents
WHERE rank_pos <= 2;

Real-World Enterprise Scenario

Calculating month-over-month revenue growth where each transaction row needs to display both the current transaction amount and the running cumulative total for the year.

Interviewer Pro Tip: Know the exact differences between ROW_NUMBER(), RANK(), and DENSE_RANK() when ties occur in ordering.

Common Candidate Mistake: Attempting to filter window functions directly in the WHERE clause instead of wrapping them inside a CTE or subquery.

Related follow-up question: What is the difference between RANK() and DENSE_RANK()?

SQL Querying & Database Architecture Interview Questions

What are SQL Window Functions and how do they differ from GROUP BY?

Visit Our Ghaziabad Training Center