30 SQL Interview Questions for Data Engineers: Window Functions to CTEs

Every data engineering interview in Pune starts the same way. Before anyone asks about Databricks, Azure Data Factory or Airflow, the interviewer opens a blank query editor and says, "Let's start with some SQL." It does not matter whether the role is at an Hinjewadi product company or a Kharadi services firm; SQL is the first filter, and it is the one most candidates underestimate.

At IntelliBI Innovations Technologies, we have sat on both sides of that table. Our mentors have run hiring rounds at MNCs, and our placement team has debriefed hundreds of learners after their interviews. The questions below are the ones that keep coming back. They are grouped the way interviewers usually progress: fundamentals, aggregation, joins, window functions, CTEs, and finally performance and scenario rounds. Work through them honestly, and you will walk into your next round with far more confidence than most candidates, whether you are a fresher, a working professional switching careers, or someone completing a Data Engineering Course in Pune right now.

Why SQL Still Decides Data Engineering Interviews in Pune

Modern data stacks are built on Spark, Delta Lake and cloud warehouses, yet almost every transformation in those systems is expressed in SQL or a SQL-like API. Spark SQL, Databricks SQL, Synapse serverless pools, Redshift and Snowflake all speak the same core language. An interviewer who checks your SQL is really checking whether you can think in sets, reason about data shape, and write logic that scales. That is exactly why the Best Data Engineering Course in Pune will spend its first weeks on SQL before touching a single cloud service, and why the Data Engineering Training in Pune at IntelliBI treats SQL as a production skill, not a prerequisite to rush through.

Section 1: SQL Fundamentals Every Data Engineer Must Nail

1. What is the difference between DDL, DML, DCL and TCL?

DDL (CREATE, ALTER, DROP) defines structure. DML (SELECT, INSERT, UPDATE, DELETE) manipulates data. DCL (GRANT, REVOKE) controls access. TCL (COMMIT, ROLLBACK, SAVEPOINT) manages transactions. Interviewers ask this to confirm you understand that a pipeline touches all four, often across different permissions.

2. Explain the logical order of execution of a SQL query.

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/TOP. This is why you cannot reference a SELECT alias inside WHERE, and why window functions (evaluated at SELECT) cannot be filtered in WHERE without a CTE or subquery.

3. What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation; HAVING filters groups after aggregation. A classic follow-up: "Can you use HAVING without GROUP BY?" Yes, it then treats the whole result as one group.

4. How do UNION and UNION ALL differ, and which is faster?

UNION removes duplicates and therefore sorts or hashes the combined set; UNION ALL simply appends. In pipelines, UNION ALL is almost always the right choice unless deduplication is a stated requirement.

5. What are NULLs, and why does NULL = NULL return no rows?

NULL represents an unknown value, so any comparison with NULL yields UNKNOWN, not TRUE. Use IS NULL, COALESCE or NULLIF. In data engineering interviews this leads into questions about how NULLs behave in joins, aggregates (COUNT(*) vs COUNT(column)) and sorting.

6. Explain primary keys, foreign keys and constraints in the context of a data warehouse.

In OLTP they enforce integrity; in many cloud warehouses they are informational only. A strong answer mentions that data engineers must enforce uniqueness and referential integrity in the pipeline, for example through Delta Lake constraints or dbt tests, because the warehouse may not.

Section 2: Aggregation and Grouping Questions

7. Find the second highest salary in an employee table.

Three accepted approaches: a subquery with MAX below the maximum, OFFSET/FETCH after ORDER BY DESC, or DENSE_RANK() in a CTE. Interviewers prefer the window version because it generalises to the Nth value and handles ties cleanly.

8. How do you find duplicate records, and how do you delete them keeping one copy?

GROUP BY the business key with HAVING COUNT(*) > 1 to find them. To delete, use ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC) in a CTE and delete where the row number is greater than one. This is a daily task in Data Engineering Classes in Pune at IntelliBI because deduplication is the first step of nearly every Bronze-to-Silver transformation.

9. What does GROUPING SETS, ROLLUP and CUBE do?

They produce multiple aggregation levels in one query. ROLLUP gives hierarchical subtotals (year, year-month, year-month-day); CUBE gives every combination; GROUPING SETS lets you pick exactly which. Useful for reporting layers feeding Power BI.

10. Count customers who placed orders in every month of 2025.

GROUP BY customer and use HAVING COUNT(DISTINCT month) = 12. This tests whether you reach for DISTINCT inside an aggregate rather than writing twelve self-joins.

Section 3: Joins and Set Logic

11. Explain INNER, LEFT, RIGHT, FULL and CROSS joins with a data-pipeline example.

INNER returns matches only; LEFT keeps all left rows; FULL keeps everything; CROSS produces a Cartesian product. A pipeline example: LEFT JOIN a fact table to a dimension so unmatched facts are not silently dropped, then route NULL dimension keys to an "unknown member" row.

12. What is a self join, and when would a data engineer use one?

A table joined to itself, typically for hierarchies (employee to manager) or comparing a row with its previous version. Mention that LAG() often replaces self joins for sequential comparisons.

13. What is an anti join and a semi join?

A semi join returns rows from the left table that have a match on the right (EXISTS or IN); an anti join returns rows with no match (NOT EXISTS or LEFT JOIN … WHERE right.key IS NULL). These are the backbone of incremental loads: "give me source rows not yet in the target."

14. Why does NOT IN behave unexpectedly with NULLs?

If the subquery returns even one NULL, NOT IN returns no rows, because the comparison becomes UNKNOWN. Use NOT EXISTS instead. Interviewers love this one because it exposes real production bugs.

15. How do you join on a date range (slowly changing dimension lookup)?

JOIN the fact on the dimension's business key AND fact.event_date BETWEEN dim.valid_from AND dim.valid_to. This is the core of SCD Type 2 reads and appears in almost every Azure Data Engineer Course in Pune interview scenario we have tracked.

Section 4: Window Functions – The Round That Separates Candidates

Window functions are where most interviews turn. They are also where learners from the Advanced Data Engineer Course in Pune at IntelliBI consistently outperform, because we practise them on real retail, healthcare and insurance datasets rather than toy tables.

16. What is a window function, and how does it differ from GROUP BY?

A window function computes a value across a set of rows related to the current row without collapsing the rows. GROUP BY reduces many rows to one; OVER() keeps every row and adds a computed column. Syntax: function() OVER (PARTITION BY … ORDER BY … ROWS BETWEEN …).

17. Explain ROW_NUMBER, RANK and DENSE_RANK with ties.

For scores 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4; RANK gives 1, 2, 2, 4; DENSE_RANK gives 1, 2, 2, 3. Use ROW_NUMBER for deduplication, DENSE_RANK for "top N distinct values."

18. What do LAG and LEAD do? Give a pipeline use case.

They read the previous or next row's value within a partition. Use case: compute day-over-day change in sensor readings, or detect a status change by comparing the current status with LAG(status).

19. How do you write a running total and a 7-day moving average?

SUM(amount) OVER (PARTITION BY customer ORDER BY order_date ROWS UNBOUNDED PRECEDING) for the running total; AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for the moving average. Be ready to explain ROWS versus RANGE: ROWS counts physical rows, RANGE groups equal ORDER BY values.

20. Find the first and last purchase per customer in one query.

FIRST_VALUE and LAST_VALUE with ORDER BY purchase_date, and for LAST_VALUE specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, otherwise the default frame stops at the current row and returns the wrong answer. Interviewers plant this trap deliberately.

21. What is NTILE, and when would you use it?

NTILE(n) distributes rows into n buckets. Useful for percentile segmentation such as splitting customers into quartiles by revenue for a marketing model.

22. Solve the "gaps and islands" problem.

To find consecutive date streaks, subtract ROW_NUMBER() from the date to create a constant for each island, then GROUP BY that constant. Being able to explain the trick, not just paste it, is what earns the mark.

23. How do window functions behave in Spark SQL and Databricks?

Identically in syntax, but a window without PARTITION BY moves all data to a single partition, which is a performance anti-pattern on large tables. This is exactly the type of question that connects Databricks Training in Pune to real interview rounds: the SQL is the same, the execution engine is not.

Section 5: CTEs, Subqueries and Recursive Logic

24. What is a CTE, and when do you prefer it over a subquery or temp table?

A Common Table Expression (WITH name AS (…)) is a named, readable, reusable query block. Prefer CTEs for readability and chained transformations; prefer temp tables when the intermediate result is large, reused many times, or needs an index. In most engines a CTE is inlined, so it is not a performance feature by itself.

25. Write a recursive CTE to flatten an organisational hierarchy.

The anchor member selects the root (manager_id IS NULL); the recursive member joins the CTE back to the table on employee.manager_id = cte.employee_id, incrementing a level column. Mention MAXRECURSION or the engine's recursion limit as a safety net.

26. Can you use multiple CTEs, and can a CTE reference another?

Yes: WITH a AS (…), b AS (SELECT … FROM a) … Each CTE can reference those defined before it. This pattern mirrors the Bronze → Silver → Gold flow taught throughout the Data Engineer Course in Pune at IntelliBI.

27. How would you implement SCD Type 2 logic in SQL using CTEs and MERGE?

Use a CTE to identify changed rows by comparing hashed attributes between source and target, then MERGE: expire the current target row (set valid_to and is_current = 0) and insert the new version. In Delta Lake, MERGE INTO handles this natively, and the interviewer will often ask you to walk through it step by step.

Section 6: Performance, Indexing and Real-World Scenarios

28. How do you read an execution plan, and what are the red flags?

Look for table scans on large tables, key lookups in loops, hash joins with huge spills, implicit conversions and missing statistics. Say explicitly that you check estimated versus actual row counts, because a large gap signals stale statistics or a bad cardinality estimate.

29. Clustered versus non-clustered indexes, and what do they mean in a cloud warehouse?

A clustered index defines physical order; non-clustered indexes are separate structures pointing back to rows. In Synapse, Redshift or Databricks the equivalents are distribution keys, sort keys, Z-ordering and liquid clustering. Showing you can translate the concept across engines is what cloud engineer courses in pune should prepare you for, and what interviewers reward.

30. A query that took two minutes yesterday takes twenty today. How do you troubleshoot?

Check for data volume growth, parameter sniffing, blocked sessions, stale statistics, index fragmentation, a changed plan, or a skewed partition if you are on Spark. Describe a method, not a guess: reproduce, compare plans, isolate the slow operator, fix, and add monitoring so it does not happen silently again.

How to Practise These SQL Interview Questions the IntelliBI Way

Reading answers is not the same as earning them. Here is the practice routine our mentors recommend, the same one used inside the Best Data Engineering Institute in Pune by learners heading into placement season:

• Rebuild every answer from scratch on a real dataset. Public retail or healthcare datasets work well; so do the capstone datasets we use in class.
• Narrate your query aloud. Interviewers judge how you reason about data as much as the final SQL.
• Time yourself. Most Pune hiring rounds give 10–15 minutes per scenario question.
• Translate each answer into Spark SQL or Databricks SQL and note what changes. This habit is why learners from our Data Engineering Training in Pune move comfortably between SQL Server, Synapse and Databricks during interviews.
• Keep a mistake log. The questions you got wrong today are the ones you will be asked tomorrow.

What Comes After SQL in a Data Engineering Interview

Once you clear the SQL round, the conversation shifts to Python, PySpark, cloud services and system design. Interviewers will ask how you would orchestrate the same transformation in Azure Data Factory or Airflow, how you would store it in a Medallion lakehouse, and how you would monitor it in production. If you are planning your preparation, treat SQL as the foundation and build the cloud layers on top of it, which is precisely how we sequence the Advanced Data Engineer Course in Pune: SQL first, then Python, PySpark, Databricks, Azure and AWS, Airflow, warehousing, and finally GenAI and Agentic AI for data engineers.

Final Word from IntelliBI

SQL interviews are not designed to trick you; they are designed to find out whether you can be trusted with production data. Thirty questions will not cover everything an interviewer might ask, but they cover the patterns behind almost everything. Master the logic behind window functions and CTEs, understand how joins and NULLs really behave, and learn to read a plan rather than guess at one, and you will be ready.

If you want to practise these questions with mentors who have conducted the interviews themselves, on real projects, with mock rounds and placement support until you are placed, explore the Data Engineer Course in Pune at IntelliBI Innovations Technologies, Thergaon, Pimpri-Chinchwad. Weekend and evening batches are available for working professionals, and the next batch starts in October 2026. Your SQL round is the first door; let us help you walk through every one after it.