Scenario Questions - Part 2

Imagine a leaderboard where multiple executives earn the exact same top-tier salary. If you simply pull the next numeric value down without accounting for ties, you risk returning an incorrect rank. The most robust modern approach is to evaluate the dataset using the DENSE_RANK window function ordered by salary descending. Wrap this evaluation inside a Common Table Expression, then query the outer layer filtering precisely where the rank equals two. This method elegantly handles duplicate values, ensuring that if two people share the absolute highest salary, the next unique value down is correctly identified as the second highest. Example: WITH RankedSalaries AS ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) SELECT salary FROM RankedSalaries WHERE rnk = 2; Interview Tip: Avoid relying on simple OFFSET 1 LIMIT 1 hacks for this question, as they fail immediately if there is a tie for the top spot.

Imagine compiling a corporate talent report where you need to showcase the elite earners within every individual department slice, without letting a massive department flood the entire list. You can solve this by partitioning your dataset by department ID and ordering the salaries in descending order within each group using the DENSE_RANK or ROW_NUMBER window functions. By wrapping this calculation inside a subquery or a CTE, the outer block can sweep through and isolate records where the computed rank is less than or equal to three, providing a perfectly balanced cross-department list. Example: WITH DeptRanks AS ( SELECT name, department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk FROM employees ) SELECT name, department_id, salary FROM DeptRanks WHERE rnk <= 3; Interview Tip: Be ready to explain the difference between ROW_NUMBER and DENSE_RANK here. If the 3rd and 4th employees have identical salaries, DENSE_RANK will pull both, whereas ROW_NUMBER will cut off exactly at three.

Imagine an operational database where a user registration glitch has accidentally generated multiple records for the exact same email address, and you need to pinpoint the problem keys. To locate these duplicates, group the table rows by the specific columns you suspect are repeated—such as email or username. Then, apply a HAVING clause that checks if the total count of rows within that grouping is strictly greater than one. This ignores all perfectly unique entries and surfaces only the exact keys that are corrupting the dataset. Example: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; Interview Tip: In an interview, follow up this answer by suggesting that you would select the primary key alongside the duplicate columns to inspect which specific row IDs are involved.

Imagine discovering dozens of completely identical transaction entries inside a ledger table, and your task is to safely wipe out the extra copies while leaving exactly one master row intact. The cleanest strategy is to construct a Common Table Expression that partitions the data by the columns causing the duplication, assigning an incremental serial value to each row via ROW_NUMBER. The ordering can be determined by a primary key or date. You can then run a direct DELETE statement targeting the CTE where the row number is strictly greater than one, which strips away the extra clones instantly. Example: WITH DuplicateDeleter AS ( SELECT id, email, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as row_num FROM users ) DELETE FROM DuplicateDeleter WHERE row_num > 1; Interview Tip: Modifying data through a CTE is highly supported in engines like SQL Server and PostgreSQL, making it a favorite answer for senior interview loops.

Imagine building an internal organizational flowchart where you need to quickly locate the CEO, founders, or top-level directors who report to no one else in the company. In a well-designed employee hierarchy table, top-level executives will typically have their manager ID column left blank. You can isolate these records by writing a straightforward query filtering for rows WHERE manager_id IS NULL. Alternatively, you can run an anti-join or a LEFT JOIN back to a secondary copy of the employee table to verify that no matching manager record exists. Example: SELECT name FROM employees WHERE manager_id IS NULL; Interview Tip: Emphasize that you must explicitly use the IS NULL operator, as standard equality comparisons like = NULL will always evaluate to unknown in SQL logic.

Imagine needing to correct a structural mistake where data entry operators accidentally flipped the values of a first_name and last_name column across an entire table. You can resolve this instantly by executing a single atomic UPDATE statement that performs multiple assignments simultaneously. Because SQL evaluates the expression values across the entire row before committing the physical changes to the disk storage engine, it safely swaps the values without mixing them up or requiring an intermediate scratchpad variable. Example: UPDATE users SET first_name = last_name, last_name = first_name; Interview Tip: This is a classic trick question. Emphasize that the relational engine handles the assignment simultaneously at the row level, preventing the first assignment from overwriting the value needed for the second.

Example: Think about a customer support log where users submit multiple tickets over time, and you need a clean summary report showing only the absolute first ticket each customer ever opened. To achieve this, deploy the ROW_NUMBER window function, partitioning the dataset by the customer identifier and sorting the records chronologically by the creation timestamp. Wrap this logic inside a CTE, and then pull rows where the row number equals one to isolate the true historical starting record for every single group. Example: WITH FirstTickets AS ( SELECT ticket_id, customer_id, ticket_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ticket_date ASC) as row_num FROM support_tickets ) SELECT ticket_id, customer_id, ticket_date FROM FirstTickets WHERE row_num = 1; Interview Tip: Mention that using ROW_NUMBER ensures you get exactly one row per group, even if two entries happen to share the exact same timestamp.

Imagine needing to print out an ordered, numbered list of products for a catalog report, but the underlying table schema does not include a sequential ID field. You can easily compute temporary, on-the-fly row sequences by utilizing the ROW_NUMBER window function within your SELECT clause. By supplying an OVER clause with an explicit ORDER BY statement, the query engine assigns a dynamic, sequential integer to every row during the execution phase, leaving the original data on disk completely untouched. Example: SELECT ROW_NUMBER() OVER (ORDER BY product_name) as line_item, product_name FROM products; Interview Tip: Highlight that these generated numbers are transient and will change automatically if the query's sorting parameters are modified.

Imagine building a search results webpage for an e-commerce platform where you need to display exactly twenty items at a time, allowing users to click through page two, page three, and beyond. Modern relational engines handle pagination naturally via windowing extensions. In PostgreSQL and MySQL, you implement this using the LIMIT and OFFSET clauses. In standard ANSI SQL and SQL Server, you use the OFFSET and FETCH NEXT commands. Both structures skip a specific number of leading rows and pull the next designated block of data. Example: SELECT product_id, title FROM products ORDER BY product_id OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY; Interview Tip: Always emphasize that an explicit ORDER BY clause is mandatory for pagination; otherwise, the database engine can return rows in a completely random sequence across pages.

Imagine managing an automated invoice system where sequential invoice numbers are generated, and you need to run a routine audit to see if any invoice numbers are missing due to deleted records. You can uncover these gaps by using a recursive CTE to build a complete, continuous sequence of baseline numbers spanning from your lowest ID to your highest ID. Then, run a LEFT JOIN from that simulated baseline sequence back to your actual transactions table, filtering for rows where the transaction side turns up NULL to expose the hidden gaps. Example: WITH RECURSIVE SequenceGenerator AS ( SELECT MIN(invoice_num) as num FROM invoices UNION ALL SELECT num + 1 FROM SequenceGenerator WHERE num < (SELECT MAX(invoice_num) FROM invoices) ) SELECT s.num FROM SequenceGenerator s LEFT JOIN invoices i ON s.num = i.invoice_num WHERE i.invoice_num IS NULL; Interview Tip: For incredibly large datasets, you can also suggest using the LEAD window function to compare a row's value to the next row's value and detect jumps greater than one.

Imagine comparing a production database table with a nightly backup copy to quickly audit which specific records were modified or updated during the day. The most elegant approach is to use set operators like EXCEPT or MINUS. By running a SELECT of the primary key and all data columns from the production table, followed by the EXCEPT operator, and then the same SELECT from the backup table, the engine filters out all identical rows and outputs only the rows containing discrepancies. Example: SELECT customer_id, address, phone FROM current_customers EXCEPT SELECT customer_id, address, phone FROM backup_customers; Interview Tip: Set operators naturally account for NULL values during comparison, making them far more reliable than writing a long chain of explicit inequality checks in a JOIN statement.

Imagine a scenario where a staging table has been populated with corrected customer addresses, and you need to push those new values into your primary operational master table. The syntax for this type of operation is highly database-dependent. In Microsoft SQL Server, you use an UPDATE statement combined directly with a FROM and JOIN clause. In PostgreSQL, the UPDATE table incorporates a specialized FROM clause, while the MERGE statement offers a cross-platform alternative. Example (SQL Server style): UPDATE c SET c.address = s.new_address FROM customers c JOIN staging_addresses s ON c.customer_id = s.customer_id; Interview Tip: Always clarify the database flavor you are targeting, as updating through a join is one of the most widely varied syntax patterns across relational platforms.

Example: Think about monitoring a temperature sensor log where the device reads the status every minute, and you want to detect errors by finding instances where the exact same reading is recorded consecutively. You can analyze these patterns by using the LAG window function to pull the column value from the immediately preceding row based on a chronological timestamp order. Once you have the current value and the past value side-by-side in a CTE, add an outer filter where the current value matches the lagged value. Example: WITH LogComparison AS ( SELECT device_id, reading, LAG(reading) OVER (PARTITION BY device_id ORDER BY log_time) as prev_reading FROM sensor_logs ) SELECT device_id, reading FROM LogComparison WHERE reading = prev_reading; Interview Tip: This is a favorite scenario for time-series analysis and fraud detection algorithms.

Imagine generating a financial ledger report for an account where you need to display a cumulative, rolling balance column that adds up transactions day by day. You can compute this efficiently by utilizing the aggregate SUM function as a window function. By appending an OVER clause containing an explicit ORDER BY statement on the transaction date, the database engine calculates a rolling running sum for each row, processing the dataset continuously without needing slow loops. Example: SELECT transaction_date, amount, SUM(amount) OVER (ORDER BY transaction_date) as running_total FROM bank_transactions; Interview Tip: Mention that omitting a PARTITION BY inside the OVER clause creates a running total across the entire table, while including it resets the running total for each specific group.

Imagine inheriting a legacy system where a critical search query takes several seconds to run, dragging down the application experience for everyone. Your first move must always be to look at the visual execution plan to track down bottlenecks like index scans or heavy sorts. Add targeted indexes on columns used in WHERE filters and JOIN conditions, and strip out performance-killing SELECT * wildcards. Rewrite predicates to ensure they are sargable by eliminating functions on indexed columns, and make sure database statistics are up-to-date so the optimizer can make smart choices. Interview Tip: Emphasize a structured workflow: measure with execution plans first, index second, and refactor query syntax third.