The #1 SQL topic in data engineering interviews. ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, PARTITION BY, and OVER()-with real questions and answers.
Window functions are the most frequently tested SQL topic in data engineering interviews. These questions cover ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG, running totals, moving averages, and complex PARTITION BY / ORDER BY patterns. Master this topic to ace the SQL round at any company.
This collection contains 35 curated questions: 4 easy, 24 medium, and 7 hard. The distribution skews toward harder problems, reflecting the depth expected in senior-level interviews.
The most frequently tested areas in this set are partition (28), window (17), sql (13), spark (11), join (7), and optimization (3). Focusing on these topics will give you the highest return on your preparation time.
Start with the easy questions to warm up and solidify fundamentals. Medium-difficulty questions form the bulk of real interviews — spend the most time here and practice explaining your reasoning out loud. Hard questions often appear in senior and staff-level rounds; attempt them after you're comfortable with the basics. For each question, try answering before revealing the solution. Use our AI Mock Interview to simulate real interview conditions and get instant feedback on your responses.
Write an SQL query to find the second-highest salary from an employee table.
Demonstrate the difference between DENSE_RANK() and RANK()
Discuss differences between ROW_NUMBER(), RANK(), and DENSE_RANK(), and provide examples from your projects.
Explain the differences between Repartition and Coalesce. When would you use each?
What strategies can you use to handle skewed data in Spark?
How do you remove duplicate rows in BigQuery?
Joins and window functions - INNER, LEFT, RIGHT, FULL OUTER, ROW_NUMBER(), RANK(), DENSE_RANK()
Convert complex SQL (CTEs, window functions, subqueries) to production-grade PySpark. Discuss when to use spark.sql() vs. DataFrame API, and the implications for testability, partitioning, and execution predictability.
Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() with examples.
Explain SQL Window Functions with examples.
Explain the concept of checkpointing in Spark and why it is important.
How would you handle duplicate records in an SQL table?
Implement a query to find the top 5 customers by total sales amount.
Prioritize Spark optimizations by impact and effort. Discuss partitioning strategy, caching policy, join selection, shuffle reduction, and when each becomes a scalability or cost bottleneck.
Retrieve the most recent sale_timestamp for each product (Latest Transaction).
SQL query to find the second highest salary from each department.
What is a window function? Explain with an example.
Write a query to find the top three highest-paid employees in each department using window functions.
Write a SQL query to find top 3 earners in each department.
Write an SQL query to find duplicate emails in a users table.
Write complex SQL queries involving multiple joins, subqueries, and data aggregation logic.
Write the PySpark code to find the second highest salary in each department.
Add a column to the Employees table that shows the name of the employee with the next higher employee_id.
Add a new column with the average salary by department.
Add Row Numbers using window function in PySpark
Aggregate surface areas and calculate cumulative surface area using the LAG function.
Calculate a 7-day moving average of orders for each city in the Swiggy database.
Can Schema Evolution lead to data inconsistencies? If so, how do you manage them?
Can you provide an example of a time when you went above and beyond for a project?
Can you share a time when you had to shift focus due to urgent tasks?
Can you share a time you faced a significant challenge and how you overcame it?
Describe a time when you had to work with a team to solve a complex problem.
Describe a time when you went above and beyond for a project or a customer.
Describe your role in a team project.
Difference between Presto vs. Spark underlying architecture
All three assign a number within each partition ordered by a column. ROW_NUMBER gives a unique sequential number with no ties (1,2,3,4). RANK gives tied rows the same number but skips the next values (1,1,3,4). DENSE_RANK gives ties the same number without skipping (1,1,2,3). Pick ROW_NUMBER for deduplication, RANK/DENSE_RANK for leaderboards depending on whether you want gaps.
Assign ROW_NUMBER() OVER (PARTITION BY <natural key> ORDER BY <recency column> DESC) and keep only rows where the number equals 1. Wrap it in a CTE or subquery (or use QUALIFY in Snowflake/BigQuery). This keeps the most recent record per key and is the canonical interview answer for removing duplicates.
GROUP BY collapses rows into one row per group, so you lose the detail rows. PARTITION BY (inside an OVER clause) computes the aggregate per group but keeps every original row, attaching the group-level value to each. Use PARTITION BY when you need both the row detail and an aggregate, such as each order alongside its customer's running total.
Add a frame to the OVER clause. A running total is SUM(amount) OVER (PARTITION BY user ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). A moving average uses a bounded frame like ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Interviewers often probe whether you know the default frame (RANGE UNBOUNDED PRECEDING) and how ties affect it.
LAG returns a value from a previous row in the ordered partition; LEAD returns a value from a following row. They're used for period-over-period comparisons — e.g., LAG(revenue) OVER (ORDER BY month) to compute month-over-month change. Both accept an offset and a default value for when no such row exists.
The Data Engineering Interview Answer Vault bundles 750+ reviewed answers into 7 focused PDF volumes — SQL, Spark, Python, System Design, Cloud, Behavioral, and Data Modeling. Study on any device, no subscription required.
800+ hands-on courses — Grokking System Design, Coding Patterns, and AI mock interviews for your DE loop.
Turn any topic or your own notes into an interactive, personalized course in 60 seconds.
The book that gets data engineers through system-design rounds. Essential reading.
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
Reading answers is step one. Get instant AI feedback on your answers, run mock interviews, and track readiness — built specifically for data engineering interviews.