PrepPilotPractise free

Advanced SQL interview questions

Window functions, CTEs, query tuning, analytics patterns

17 questions · OVER() · updated November 2026

Practise these as flashcards. One or two lines each; the ones you miss come back first.

Start free

Core questions

What is a window function in SQL and how does it differ from a regular aggregate function?

A window function performs calculations across a set of rows related to the current row, but unlike aggregate functions, it doesn't collapse rows. Instead, it adds a computed column while retaining all original rows, making it ideal for running totals, rankings, and comparisons.

How would you use LAG to compare current and previous sales in a table?

I would use the LAG function with an ORDER BY clause on the date column to access the previous row's sales value. For example, LAG(sales, 1) OVER (ORDER BY date) gives me the previous day's sales for each row.

Explain what a CTE is and give a simple use case.

A Common Table Expression (CTE) is a temporary result set defined within a query using the WITH clause. It's useful for breaking complex queries into readable parts, such as calculating intermediate aggregates before a final SELECT.

What does the PARTITION BY clause do in a window function?

PARTITION BY divides the result set into groups, and the window function is applied independently within each group. For example, calculating a running total per user would use PARTITION BY user_id.

Intermediate questions

How would you find the top 2 highest-paid employees in each department?

I'd use ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) to rank employees within each department, then filter for rows where the rank is less than or equal to 2.

Describe how you would pivot row data into columns using SQL.

I can use conditional aggregation with CASE statements inside aggregate functions, or use the PIVOT operator if supported. For example, summing sales by region with SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) for each region column.

What is a recursive CTE, and when would you use one?

A recursive CTE is a CTE that references itself, allowing you to perform recursive operations like traversing hierarchies or graphs. I'd use it for problems like finding all subordinates in an org chart or computing paths in a tree structure.

How do you deduplicate rows in SQL, keeping only the latest record per key?

I use ROW_NUMBER() OVER (PARTITION BY key_column ORDER BY timestamp DESC) to assign a rank, then select rows where the rank is 1. This keeps only the most recent record for each key.

Explain the difference between hash join, merge join, and nested loop join.

A hash join builds a hash table on one input and probes it with the other, efficient for large, unsorted tables. Merge join requires both inputs to be sorted and is very fast for large, sorted data. Nested loop join compares each row from one table to all rows in the other, best for small tables or when indexes are available.

What is partition pruning and why is it important in data warehouses?

Partition pruning is when the query engine scans only relevant partitions based on query filters, skipping others. It's crucial for performance in large datasets because it reduces the amount of data read and speeds up queries.

How does clustering improve query performance in warehouses like BigQuery or Snowflake?

Clustering organizes data within partitions based on specified columns, so related rows are stored together. This allows the engine to skip irrelevant data blocks during scans, improving filter and join performance.

What are the trade-offs between using a recursive CTE and a procedural loop for hierarchical data?

Recursive CTEs are set-based and run entirely in the database, making them efficient for moderate recursion depths. However, they can be less performant or hit limits with very deep hierarchies, where procedural loops outside SQL might be more flexible but less efficient.

Senior questions

Describe how you would implement a Slowly Changing Dimension (SCD) Type 2 in SQL.

See the answer in the app, free

How do you analyze and tune a slow SQL query in a data warehouse?

See the answer in the app, free

How would you detect and fill gaps in a time series using SQL?

See the answer in the app, free

Explain the 'gaps and islands' problem and how you would solve it in SQL.

See the answer in the app, free

How do you ensure efficient top-N queries in a partitioned warehouse table?

See the answer in the app, free

More Data Engineering topics