Medium-level sql questions from real data engineering interviews.
These medium sql questions are selected from real interviews at top companies. Each question includes a detailed expert answer and pro tip to help you nail your interview. This set leans toward the medium-difficulty band most real interviews actually live in (60 of 60). Recurring themes are partition, join, and sql — these patterns appear most often in real interviews and reward the deepest preparation. These questions have been reported across 51 companies including Swiggy and Accenture. Average answer is around 1 minute of reading — plan roughly 1 hour to work through the full set thoughtfully.
This collection contains 60 curated questions: 0 easy, 60 medium. The balanced mix of difficulties makes this set suitable for engineers at any career stage.
The most frequently tested areas in this set are partition (35), join (26), sql (18), window (15), snowflake (8), and bigquery (7). Focusing on these topics will give you the highest return on your preparation time.
Medium-difficulty questions form the bulk of real interviews — spend the most time here and practice explaining your reasoning out loud. 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 Data Warehouse, Data Lake, and Delta Lake
Explain the differences between Repartition and Coalesce. When would you use each?
What is the difference between partitioning and bucketing in Spark, and when would you use bucketing?
Can you explain the difference between OLTP and OLAP?
Describe a time when you had to optimize a slow SQL query. What steps did you take?
Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
How do you handle NULL values in SQL? Mention functions like COALESCE and NULLIF.
What is the difference between WHERE and HAVING clauses in SQL?
Describe a scenario where partitioning and bucketing would improve query performance.
Explain the types of triggers in ADF, including schedule, tumbling window, and event-based triggers.
How do you remove duplicate rows in BigQuery?
When would you choose a Snowflake schema over a Star schema?
Detail examples of inner, outer, left, and right joins.
Difference between ROW_NUMBER(), RANK(), and DENSE_RANK() with examples.
Difference between where and having clause with examples.
Explain SQL Window Functions with examples.
Explain the use of the MERGE statement in SQL.
How do you handle NULL values in SQL? Mention functions like COALESCE and ISNULL.
How would you handle duplicate records in an SQL table?
Implement a query to find the top 5 customers by total sales amount.
SQL query to find the second highest salary from each department.
What are primary keys and foreign keys? Why are they important?
What is a self-join, and when would you use it?
What is normalization and denormalization? When would you use each?
What is the difference between a view and a materialized view?
Write an SQL query to find duplicate emails in a users table.
Triggers in ADF, especially tumbling window triggers.
What is a window function? Explain with an example.
What is the difference between OLTP and OLAP?
Write a SQL query to find top 3 earners in each department.
Write a query to find the top three highest-paid employees in each department using window functions.
Write complex SQL queries involving multiple joins, subqueries, and data aggregation logic.
Add Row Numbers using window function in PySpark
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 manager names for each employee using a self-join.
Add a new column with the average salary by department.
Calculate the cumulative transaction amount for each month using a transaction table.
Compare Airflow's @daily vs once trigger scheduling.
Compare OLTP and OLAP systems in the context of financial transactions.
Compare PostgreSQL vs Snowflake. How do they handle duplicate record errors?
Compare the star schema and snowflake schema. Which one would you use for reporting at Swiggy, and why?
Connecting BigQuery with Linux
Explain CTE vs Temp Table. What are the differences and use cases?
Explain Data Modeling SCD Types (Type 1, 2, 3).
Implement a recursive query for hierarchy (employee-manager). Explain the termination guarantees, depth limits, and when a recursive CTE becomes a scalability bottleneck. What alternatives exist for graph-scale hierarchies in Spark or a data lake?
Compare Glue partition discovery with Hive MSCK/ADD PARTITION. Explain the operational and cost implications of crawler-based vs. partition-projection approaches. When does partition projection become necessary, and what are its limitations?
Explain how partitioning and bucketing in Hive/Spark optimize queries. What are the trade-offs in bucket count, partition cardinality, and small-file problem? When does over-partitioning or over-bucketing become counterproductive?
Explain the concept of window functions in SQL and provide an example
Explain the difference between Star and Snowflake schemas. When would you choose one over the other?
Explain the difference between partition count and query performance in Spark.
Find average salary for each manager – Assume a table with manager_id and employee_salary
Find each student's previous year's scores using the LAG function.
Find the third-highest salary for each department.
Finding nth Highest Salary within each department
Given a table with 10 records and another with 4 records, how many records result from a cross join?
Given two dataframes (df1: id, name and df2: id, country, address, city, count), join them, filter for rows where country = 'Singapore', and pivot the output. Sort cities in descending order of population count
How can you delete partitions from a table in Hive using a command?
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.