Interview Prep
SQL Interview Questions — Advanced
Advanced SQL interview preparation focuses on complex data manipulation using window functions, recursive logic, and set-based operations. These techniques allow developers to solve intricate analytical problems without relying on slow, procedural cursor-based logic. Mastering these concepts is essential for optimizing query performance and handling hierarchical or time-series data structures in large-scale production environments.
Mastering Window Functions for Running Totals
Window functions are the cornerstone of advanced SQL proficiency, allowing you to perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, which collapse rows into a single output, window functions retain the original granularity. A common interview request involves calculating a running total or a moving average. By using the 'OVER' clause with 'ORDER BY', you instruct the engine to build a cumulative sum as it traverses the data. This is computationally efficient because the database handles the state management internally, rather than forcing you to join the table against itself. The 'ROWS BETWEEN' clause is particularly important here, as it defines the window boundary, allowing you to look back at previous rows or forward to subsequent ones without performing complex, expensive self-joins on large datasets.
-- Calculate a running total of sales per employee over time
SELECT
employee_id,
sale_date,
amount,
-- Sum amount over the partition, ordering by date to create the running total
SUM(amount) OVER (PARTITION BY employee_id ORDER BY sale_date) as running_total
FROM sales_data;Handling Hierarchies with Recursive CTEs
Recursive Common Table Expressions (CTEs) are the go-to solution for traversing hierarchical data, such as organizational charts or category trees. A recursive CTE consists of two parts: an anchor member that starts the initial result set, and a recursive member that references the CTE itself to find children of the previous set. The process continues until the recursive member returns no additional rows. The database engine maintains a work table to keep track of the results. It is vital to understand that the recursive part must contain a termination condition, often implicitly handled when no new rows are joined to the prior results. If not careful, you might create an infinite loop. Recursive queries are essential when you need to resolve relationships that have an unknown depth, which standard joins simply cannot handle without hardcoding every possible level of the hierarchy.
-- Find all subordinates for a specific manager using recursion
WITH RECURSIVE Subordinates AS (
-- Anchor: start with the manager
SELECT id, name, manager_id FROM employees WHERE id = 101
UNION ALL
-- Recursive: find employees who report to those in the previous iteration
SELECT e.id, e.name, e.manager_id
FROM employees e
INNER JOIN Subordinates s ON e.manager_id = s.id
)
SELECT * FROM Subordinates;Advanced Pivoting and Conditional Aggregation
In reporting tasks, you are often asked to reshape long-format data into a wide-format matrix, which is known as pivoting. While some systems have built-in pivot commands, the standard and most portable way to achieve this is through conditional aggregation. By using a 'CASE' statement inside an aggregate function like 'SUM' or 'MAX', you can create a dedicated column for each category value you are tracking. This technique is extremely powerful because it allows for granular control over the data transformation process, including the ability to apply filters or calculations on a per-category basis. Understanding the logic of conditional aggregation is a key differentiator in interviews because it demonstrates an ability to translate business-facing report requirements into performant, set-based logic. It reduces the need for application-level data formatting, keeping the computational burden on the database server.
-- Pivot sales data to see monthly totals per department
SELECT
department_id,
SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) as jan_total,
SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) as feb_total
FROM monthly_sales
GROUP BY department_id;Efficient Deduplication with Window Functions
Data cleaning is a recurring theme in technical interviews, specifically the task of identifying and removing duplicate records while keeping a single representative row. A naive approach might involve temporary tables or multiple grouping operations. A more advanced, performant solution uses the 'ROW_NUMBER()' window function to assign a unique incrementing integer to rows within a partition of duplicates. By partitioning by the columns that define a 'duplicate' and ordering by a criterion like 'created_at DESC', you can easily isolate the most recent or the oldest record. The final query simply filters for rows where the row number equals one. This approach is highly efficient because it avoids the overhead of complex subqueries and leverages the internal sorting mechanisms of the database engine, ensuring that your data integrity tasks remain performant even as your dataset scales significantly.
-- Delete duplicate entries, keeping only the most recent one
DELETE FROM log_entries
WHERE id IN (
SELECT id FROM (
SELECT id, ROW_NUMBER() OVER(PARTITION BY user_id, event_type ORDER BY timestamp DESC) as rn
FROM log_entries
) sub WHERE rn > 1
);Optimizing Queries with Exists and Semi-Joins
When filtering a primary dataset based on the presence of related data in another table, many developers default to 'IN' clauses or 'JOIN' operations. However, 'EXISTS' is often significantly more performant, especially when checking for the existence of related records in large tables. The reason is that 'EXISTS' is a semi-join; the database engine can stop scanning the secondary table as soon as it finds a single match for the current row. Unlike a regular 'INNER JOIN', which might multiply rows if there are multiple matches in the secondary table (requiring a subsequent 'DISTINCT' to fix), 'EXISTS' provides a boolean evaluation that maintains the cardinality of the primary table. This is a critical distinction that performance-oriented interviewers look for, as it highlights your understanding of how query execution plans process nested loops and semi-join optimizations behind the scenes.
-- Efficiently find customers who have placed at least one order
SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);Key points
- Window functions allow for complex calculations across rows without collapsing the result set.
- Recursive CTEs are the standard approach for navigating unknown depths in hierarchical datasets.
- Conditional aggregation is the most portable and flexible method for performing data pivoting.
- The 'ROW_NUMBER' function is the most efficient way to isolate and remove duplicate rows.
- Using 'EXISTS' acts as a semi-join, improving performance by stopping searches early upon match discovery.
- Always define boundary conditions carefully in recursive queries to prevent infinite processing loops.
- Window partitioning allows logic to reset based on specific dimensions like category or time period.
- Prioritize set-based operations over procedural iterations to maintain performance at scale.
Common mistakes
- Mistake: Using COUNT(*) when you only need to check for existence. Why it's wrong: It forces the database to scan or count all rows, which is slow on large tables. Fix: Use EXISTS(SELECT 1 ...) to stop searching once the first match is found.
- Mistake: Placing filter conditions in the ON clause for outer joins when they belong in the WHERE clause. Why it's wrong: In a LEFT JOIN, this alters the result set by filtering the right table before the join, inadvertently turning it into an INNER JOIN. Fix: Ensure conditions affecting the right table are in the WHERE clause.
- Mistake: Overusing cursors for row-by-row processing. Why it's wrong: SQL is designed for set-based operations; cursors are procedural and significantly slower. Fix: Rewrite the logic using Common Table Expressions (CTEs) or window functions.
- Mistake: Neglecting the order of operations in SQL. Why it's wrong: Many developers forget that WHERE is processed before GROUP BY, meaning aggregate results cannot be filtered in the WHERE clause. Fix: Use the HAVING clause for filtering after grouping.
- Mistake: Using SELECT * in production queries. Why it's wrong: It increases I/O overhead and can break application code if schema changes occur unexpectedly. Fix: Explicitly define the required columns in the SELECT statement.
Interview questions
What is the difference between a RANK() and a DENSE_RANK() window function in SQL?
Both RANK() and DENSE_RANK() are used to assign a numerical order to rows within a result set based on a specified sort order, but they handle ties differently. When using RANK(), if two rows share the same value, they receive the same rank, but the next rank in the sequence is skipped. For example, if two rows are tied for rank 1, the next row will be rank 3. DENSE_RANK(), however, does not skip any numbers; it assigns the same rank to ties and assigns the very next integer to the following row. You would use RANK() when you need to reflect a gap in positions, such as in sports, while DENSE_RANK() is better for situations where you want an unbroken sequence of identifiers, regardless of the number of duplicate values present in the data set.
Explain the performance implications and use cases for using a CTE versus a temporary table in complex queries.
Common Table Expressions (CTEs) are essentially temporary result sets that exist only during the execution of a single query. They improve code readability and maintainability for recursive operations. In contrast, temporary tables are physical structures stored in the database's tempdb, allowing you to index, analyze, and repeatedly reference data throughout a multi-step stored procedure. I would recommend using a CTE for simple, one-time logical abstractions where clarity is the priority. However, if the query processes a massive volume of data that requires multiple scans or complex filtering, a temporary table is superior because you can create indexes on it, which significantly reduces I/O overhead and speeds up subsequent joins compared to the ephemeral nature of a CTE.
How does the 'EXISTS' clause differ from using 'IN' when filtering data based on a subquery?
The primary difference lies in how the database engine handles the subquery processing. The IN operator forces the database to evaluate the entire subquery first, creating a list of values to match against the outer query. This can lead to performance degradation if the subquery returns a large set of values. The EXISTS operator, however, uses semi-join logic; it evaluates the subquery and stops as soon as it finds the first matching row, returning a boolean true. Consequently, EXISTS is generally more efficient when dealing with large datasets or correlated subqueries because it doesn't need to load the entire result set into memory or perform a complete list scan before proceeding with the outer query execution.
What is the purpose of a cross apply versus an outer apply, and when would you use them?
CROSS APPLY and OUTER APPLY are used to join a table to a table-valued function or a subquery that references columns from the outer table. CROSS APPLY acts like an INNER JOIN; it only returns rows from the left table if the right-side function returns at least one row. Conversely, OUTER APPLY acts like a LEFT JOIN; it returns all rows from the left table, even if the right-side function returns an empty set, effectively filling the missing values with NULLs. I typically use these when I need to apply complex logic, such as getting the top N records per category for each row in a main table, as these operators allow for correlated parameter passing which standard joins cannot easily accommodate.
Describe the mechanics of a recursive CTE and provide a scenario where it is essential.
A recursive CTE consists of two parts: the anchor member and the recursive member, joined by a UNION ALL. The anchor member initializes the result set, and the recursive member repeatedly references the CTE name to build upon the previous result until no more rows are returned. This is essential for traversing hierarchical data structures, such as an organizational chart where you need to report all subordinates for a specific manager at any depth. Without a recursive CTE, you would be forced to use multiple self-joins, which is inefficient, difficult to read, and limited by the hardcoded number of joins you write; recursion allows the query to dynamically handle any depth of the hierarchy.
How do you optimize a query that is suffering from parameter sniffing issues?
Parameter sniffing occurs when the query optimizer creates an execution plan based on the first parameter value passed during compilation, which may be suboptimal for subsequent executions with different data distributions. To mitigate this, you can use the OPTIMIZE FOR UNKNOWN query hint, which forces the optimizer to use statistical averages rather than a specific parameter value. Alternatively, you can use the RECOMPILE hint if the query is run infrequently, or implement local variables within your procedure to break the parameter sniffing. I prefer using OPTIMIZE FOR UNKNOWN for high-frequency queries to ensure a stable, if not always perfect, execution plan that prevents catastrophic performance regressions caused by drastic shifts in the selectivity of the filtered data.
Check yourself
1. When comparing a NULL value using the standard equals operator in a WHERE clause, what is the result?
- A.True
- B.False
- C.Unknown
- D.Null
Show answer
C. Unknown
In SQL, NULL represents an unknown value. Comparing anything to NULL using '=' results in 'Unknown' (or UNKNOWN), which evaluates to false in a WHERE filter. 'False' and 'Null' are incorrect because they imply a boolean or defined result, while the standard defines this state as Unknown.
2. What is the primary difference between a RANK() and a DENSE_RANK() window function?
- A.RANK() skips numbers, DENSE_RANK() does not
- B.DENSE_RANK() skips numbers, RANK() does not
- C.RANK() works on strings, DENSE_RANK() only on numbers
- D.They are identical in functionality
Show answer
A. RANK() skips numbers, DENSE_RANK() does not
RANK() leaves gaps in the ranking sequence when ties occur (e.g., 1, 2, 2, 4), whereas DENSE_RANK() continues the sequence without gaps (e.g., 1, 2, 2, 3). Option 1 describes this correctly, while others misidentify the behavior or capabilities.
3. How does a Common Table Expression (CTE) differ from a subquery?
- A.CTEs are always faster than subqueries
- B.CTEs can be referenced multiple times within a single statement
- C.Subqueries cannot be used in JOIN clauses
- D.CTEs must be written as materialized tables
Show answer
B. CTEs can be referenced multiple times within a single statement
The key advantage of a CTE is its ability to be referenced multiple times within the same query, improving readability and modularity. CTEs are not inherently faster, subqueries can be joined, and CTEs are typically not materialized unless specified by the engine.
4. Which of the following describes the execution order of a SQL query involving GROUP BY?
- A.SELECT -> FROM -> WHERE -> GROUP BY
- B.FROM -> WHERE -> GROUP BY -> SELECT
- C.FROM -> SELECT -> WHERE -> GROUP BY
- D.WHERE -> FROM -> GROUP BY -> SELECT
Show answer
B. FROM -> WHERE -> GROUP BY -> SELECT
SQL logically processes the FROM clause first, then filters via WHERE, groups rows with GROUP BY, and finally projects the columns via SELECT. Other options reverse or mix these steps, which is logically impossible for the engine.
5. When using an INNER JOIN, what happens to rows in either table that do not have a match in the other?
- A.They are included with NULL values for the missing data
- B.They cause an error
- C.They are excluded from the result set
- D.They are included but marked with a flag
Show answer
C. They are excluded from the result set
INNER JOIN only returns rows where there is a match in both participating tables. NULL padding only occurs in OUTER JOINs. No error is thrown, and no flags are applied; the rows simply do not meet the join criteria.