SQL Window Functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD Interview Guide

SQL window functions fundamentals are essential for advanced analytics and are heavily tested in senior data engineer interviews. Window functions solve problems that GROUP BY cannot handle: ranking rows, comparing values to previous rows, and calculating running totals while preserving original row detail.

Understanding SQL Window Functions Fundamentals

SQL window functions fundamentals operate on a “window” of rows defined by an OVER clause. Unlike GROUP BY which reduces rows, window functions add computed columns to every row. This distinction is critical: window functions preserve original data while adding analytical columns.

Basic syntax: SELECT column, ROW_NUMBER() OVER (ORDER BY date) AS rn FROM table. The OVER (ORDER BY date) defines the window. ROW_NUMBER() calculates a rank for each row based on date order. Every row gets a rn value; no rows are removed.

ROW_NUMBER vs RANK vs DENSE_RANK

SQL window functions fundamentals teach three ranking functions with subtle but critical differences. ROW_NUMBER assigns sequential numbers: 1, 2, 3, 4, 5. RANK handles ties by skipping numbers: 1, 2, 2, 4, 5. DENSE_RANK handles ties without gaps: 1, 2, 2, 3, 4.

PARTITION BY: Grouping Within Windows

PARTITION BY divides rows into separate groups, and window functions calculate independently for each group. SQL window functions fundamentals rely heavily on PARTITION BY to solve multi-level analytics.

LAG and LEAD: Accessing Previous and Future Rows

LAG(column, offset, default) returns the value from a previous row. LEAD(column, offset, default) returns the value from a future row. These SQL window functions fundamentals enable time-series analysis impossible with other techniques.

FIRST_VALUE and LAST_VALUE for Window Extremes

FIRST_VALUE returns the first row value in the window. LAST_VALUE returns the last. SQL window functions fundamentals use these for comparisons like “how does todays sales compare to the months first day?”

Window Frames: Controlling Row Ranges

SQL window functions fundamentals include controlling which rows are included in calculations via window frames. Default: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (all rows from start to current).

5 Complete Interview Questions with Solutions

Question 1: ROW_NUMBER for Deduplication

Customer transactions with duplicates (same customer, date, amount). Remove duplicates keeping only first occurrence using ROW_NUMBER.

WITH ranked_txns AS (
  SELECT *, ROW_NUMBER() OVER
    (PARTITION BY customer_id, date, amount ORDER BY txn_id) AS rn
  FROM transactions
)
SELECT * FROM ranked_txns WHERE rn = 1;

Explanation: ROW_NUMBER assigns 1 to first occurrence, 2+ to duplicates. Partition by duplicate key columns. WHERE rn = 1 keeps only first. This SQL window functions fundamentals technique is common for data cleaning.

Google – Senior Data AnalystAmazon – Analytics EngineerMeta – Senior Analytics Engineer

Question 2: LAG for Time-Series Analysis

Calculate day-over-day change in page views. Identify days with > 50% drop (potential incident) using LAG and CASE.

SELECT date, page_views,
       LAG(page_views) OVER (ORDER BY date) AS prev_day,
       ROUND(100 * (page_views - LAG(page_views) OVER (ORDER BY date))
             / LAG(page_views) OVER (ORDER BY date), 2) AS pct_change
FROM page_views
ORDER BY date;

Explanation: LAG retrieves previous days views. Calculate percentage change. CASE flags anomalies. This SQL window functions fundamentals pattern detects anomalies in time-series data.

Microsoft – Analytics EngineerAirbnb – Senior Data AnalystFlipkart – BI Engineer

Question 3: RANK vs DENSE_RANK

Rank employees by salary within department. Identify top 10%. Use RANK, DENSE_RANK, show difference and impact on percentile calculation.

DENSE_RANK avoids gaps in percentiles (crucial for accurate top-10% calculation).
RANK would skip percentiles with ties. This distinction affects business logic.

Google – Senior Analytics EngineerLinkedIn – BI EngineerUber – Senior Data Analyst

Question 4: Window Functions vs GROUP BY

Calculate total sales per month AND rank each transaction by amount within month. Why cant GROUP BY do this alone?

Window functions preserve original rows while adding aggregate columns. GROUP BY removes transaction-level detail.

Amazon – Senior Analytics EngineerGoogle – BI Engineer

Question 5: Running Total with Frame

Calculate running total revenue by date AND 7-day moving average. Show frame difference for each approach.

Running total uses UNBOUNDED PRECEDING to current (growing window).
Moving average uses 6 PRECEDING + CURRENT (fixed 7-day window).

Meta – Senior Analytics EngineerMicrosoft – BI Engineer

15 Practice Questions (Test Yourself)

Question 6: Your page_views table has 365 days of data. ROW_NUMBER OVER (ORDER BY date) gives 1-365. LAG(page_views) on day 1 returns NULL. Why? How would you handle this?

Google – Analytics EngineerAmazon – Senior BI Engineer

Question 7: Rank 10,000 transactions by amount within each of 50 regions using DENSE_RANK. How many transactions might have rank 1 in each region?

Meta – BI EngineerFlipkart – Analytics Engineer

Question 8: ROW_NUMBER gives 1,2,3,4,5 but RANK gives 1,2,2,4,5 for same data (two rows tied at rank 2). When would each be required in reports?

Microsoft – Senior Analytics EngineerAirbnb – Data AnalystLinkedIn – Senior BI Engineer

Question 9: Find top 3 products per region by revenue using ROW_NUMBER with PARTITION BY. How would you filter results and keep aggregation correct?

Amazon – Analytics EngineerOYO – Senior BI Engineer

Question 10: LAST_VALUE without frame specification returns current row, not actual last. What frame fixes this and why is it default otherwise?

Stripe – Senior Data AnalystUber – BI EngineerAmazon – Analytics Engineer

Question 11: Revenue table has 1M rows. Running total (SUM with UNBOUNDED PRECEDING) takes 45 seconds, window frame (ROWS 6 PRECEDING) takes 3 seconds. Why the 15x difference?

Meta – Analytics EngineerMicrosoft – Senior BI Engineer

Question 12: LEAD(salary, 1) gives next employee salary, LEAD(salary, 3) gives salary 3 rows ahead. When predicting churn by comparing current to future salary?

Google – BI EngineerFlipkart – Senior Analytics EngineerLinkedIn – Data Analyst

Question 13: PARTITION BY department, ORDER BY salary DESC gives ranks 1-500 per department (avg 100 employees/dept). Calculate percent_of_dept_max for each employee using window functions.

Airbnb – Senior Analytics EngineerAmazon – BI Engineer

Question 14: Compare efficiency: window functions vs self-join for row-level aggregation (50M rows). Which scales better and why?

Google – Senior Data AnalystMeta – Analytics EngineerOYO – Senior BI Engineer

Question 15: NTILE(4) divides 1000 rows into 4 equal buckets (250 each). Why not just use MOD or CASE with counts? When is NTILE necessary?

Microsoft – Analytics EngineerStripe – BI Engineer

Question 16: Calculate percent_of_total revenue per product within each month (1000 products, 36 months). Use window SUM and PARTITION. Row count?

Amazon – Senior Analytics EngineerGoogle – BI EngineerAirbnb – Senior Data Analyst

Question 17: ROWS BETWEEN 0 PRECEDING AND CURRENT ROW is default. Change to 100 PRECEDING for moving window. Whats the semantic difference?

Google – Senior Analytics EngineerMeta – Senior BI EngineerUber – Analytics Engineer

Question 18: FIRST_VALUE(salary) and LAST_VALUE(salary) within PARTITION BY department ORDER BY hire_date. What salary ranges do you capture?

Flipkart – Analytics EngineerLinkedIn – Senior BI EngineerAmazon – Analytics Engineer

Question 19: 100K daily events with 50 event_types. ROW_NUMBER OVER (PARTITION BY event_type ORDER BY timestamp). How many rows per event_type on average?

Airbnb – Senior Analytics EngineerAmazon – BI Engineer

Question 20: Why cant you use window functions in WHERE clause? Combine with CTEs to filter ranked results (top 5 products per region from 10M rows).

Google – Senior Data AnalystLinkedIn – Analytics EngineerOYO – Senior BI Engineer

Ready to Master SQL Window Functions & All Analytics Concepts?

These 20 practice questions are just the tip of the iceberg. Get our comprehensive Data Analytics: Ace All The Interview Concepts course.