Last Minute SQL Interview Prep Kit: Master Class on De-duplication, Joins, Window Functions, and Recursive CTEs
This chapter provides a comprehensive, production-grade guide to cracking SQL interviews for both product- and service-based companies. It covers fundamental patterns, edge cases, join mathematics, window functions, and advanced recursive Common Table Expressions (CTEs).
1. Finding Duplicates in a Table
Identifying duplicate records is a fundamental SQL task. To find duplicate values, you must group the data by the column(s) defining the duplicate criteria and count the occurrences.
The Core Mechanism
To filter aggregated results (such as COUNT()), you cannot use a WHERE clause because WHERE filters rows before aggregation. Instead, you must use the HAVING clause, which filters groups after aggregation.
Single-Column Duplicates
If duplicates are defined by a single column (e.g., emp_id):
SELECT
emp_id,
COUNT(1) AS occurrence_count
FROM emp
GROUP BY emp_id
HAVING COUNT(1) > 1;
Multi-Column Duplicates
If duplicates are defined by a combination of columns (e.g., finding identical transactions by the same customer on the same date):
SELECT
customer_id,
transaction_date,
COUNT(1) AS occurrence_count
FROM transactions
GROUP BY customer_id, transaction_date
HAVING COUNT(1) > 1;
2. De-duplication Strategies Using CTEs and Window Functions
While finding duplicates requires grouping, deleting duplicates while preserving exactly one representative record requires a more surgical approach. The industry-standard pattern uses a Common Table Expression (CTE) combined with the ROW_NUMBER() window function.
Real-Life Analogy
Think of a ticket counter where people with the same name are standing in line. To clean up the line, you give everyone a sequential ticket number starting from 1 for each unique name group. You then tell everyone whose ticket number is greater than 1 to leave the line.
Step-by-Step Evolution
Step 1: Assign Sequential Row Numbers
We partition the data by the columns that define a duplicate and order them by a unique identifier (like an auto-incrementing id or created_at timestamp) to determine which record to keep.
SELECT
id,
emp_id,
ROW_NUMBER() OVER (
PARTITION BY emp_id
ORDER BY id ASC
) AS rn
FROM emp;
Step 2: Wrap in a CTE and Delete
In databases like SQL Server, you can directly execute a DELETE statement against a CTE. The database engine resolves the underlying rows and deletes them.
WITH DeDupCTE AS (
SELECT
id,
emp_id,
ROW_NUMBER() OVER (
PARTITION BY emp_id
ORDER BY id ASC
) AS rn
FROM emp
)
DELETE FROM DeDupCTE
WHERE rn > 1;
Note: If you want to keep the latest record instead of the oldest, change the window function's sorting order to
ORDER BY id DESC.
3. UNION vs. UNION ALL
Combining datasets vertically is done using UNION or UNION ALL. Understanding their differences is critical for both query correctness and performance optimization.
Execution Flow Comparison
Code Demonstration
-- Preserves every single row from both tables
SELECT manager_id FROM emp
UNION ALL
SELECT manager_id FROM emp1;
-- Performs a distinct operation over the combined result set
SELECT manager_id FROM emp
UNION
SELECT manager_id FROM emp1;
4. Deep Dive: ROW_NUMBER vs. RANK vs. DENSE_RANK
Window functions are essential for ranking tasks. The choice between ROW_NUMBER(), RANK(), and DENSE_RANK() depends on how you want to handle ties.
Definitions
ROW_NUMBER(): Assigns a unique, sequential integer to each row. If there is a tie, the assignment is non-deterministic unless a tie-breaking column is added to theORDER BYclause.RANK(): Assigns the same rank to ties. However, it skips subsequent ranks to account for the tied rows. The next rank assigned is equal to the number of preceding rows.DENSE_RANK(): Assigns the same rank to ties without skipping any ranks. The ranks are sequential and compact.
Comparative Synthesis
Code Implementation
SELECT
emp_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM emp;
Tie-Breaking and Partitioning
To guarantee deterministic behavior with ROW_NUMBER(), or to establish secondary ranking criteria, add a tie-breaking column to the ORDER BY clause:
-- If salaries are equal, the younger employee gets the higher rank
SELECT
emp_name,
salary,
age,
ROW_NUMBER() OVER (ORDER BY salary DESC, age ASC) AS deterministic_rn
FROM emp;
To calculate ranks within specific groups (e.g., finding the highest-paid employee in each department), use the PARTITION BY clause:
SELECT
dept_id,
emp_name,
salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM emp;
5. Anti-Joins: Finding Missing Records
A common interview question asks you to find records in one table that do not exist in another (e.g., identifying employees whose departments are missing from the department master table).
Approach 1: Subquery with NOT IN (Naive)
SELECT *
FROM emp
WHERE department_id NOT IN (
SELECT department_id
FROM department
);
Warning: If the subquery returns even a single
NULLvalue, the entireNOT INquery will return zero rows. This happens becauseNULLcomparisons in SQL evaluate toUNKNOWN, which invalidets theNOT INcondition.
Approach 2: Left Anti-Join (Recommended)
A LEFT JOIN returns all records from the left table and matching records from the right table. If there is no match, the right table's columns return as NULL. Filtering for these NULL values isolates the unmatched records.
SELECT
e.emp_id,
e.emp_name,
e.department_id
FROM emp e
LEFT JOIN department d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
6. Finding the Nth Highest Salary in Each Department
To find the -th highest salary (e.g., the second highest) within each department, use a ranking window function inside a CTE, then filter the results.
WITH SalaryRanking AS (
SELECT
emp_name,
department_id,
salary,
DENSE_RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rnk
FROM emp
)
SELECT
department_id,
emp_name,
salary
FROM SalaryRanking
WHERE rnk = 2;
Handling Ties
- Using
DENSE_RANK()ensures that if multiple employees share the second-highest salary, all of them are returned. - If you need exactly one record per department even in the case of a tie, use
ROW_NUMBER()and add a tie-breaking column (e.g.,ORDER BY salary DESC, emp_id ASC).
7. Case-Insensitive Search & Data Standardization
Real-world data often has inconsistent casing (e.g., "Shilpa", "shilpa", "SHILPA"). While some database collations are case-insensitive by default, writing robust, database-agnostic queries requires explicit string standardization.
-- Standardizing search using UPPER()
SELECT *
FROM transactions
WHERE UPPER(customer_name) = 'SHILPA';
-- Standardizing search using LOWER()
SELECT *
FROM transactions
WHERE LOWER(customer_name) = 'shilpa';
8. Self-Joins: Employee-Manager Relationships
A self-join is a join where a table is joined with itself. This is useful for querying hierarchical data stored in a single table, such as an employee table where each row contains a reference to a manager's employee ID.
Real-Life Analogy
Imagine a room full of employees. Each employee has a badge with their own ID and their manager's ID. To find out who earns more than their manager, you make a copy of the room. In Room A (Employees), you look at an employee's manager ID, find the person with that matching badge ID in Room B (Managers), and compare their salaries.
Query: Find Employees Earning More Than Their Managers
SELECT
e.emp_name AS employee_name,
e.salary AS employee_salary,
m.emp_name AS manager_name,
m.salary AS manager_salary
FROM emp e
INNER JOIN emp m
ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;
9. Swapping Values in a Single Query (Conditional Updates)
A common interview question asks you to swap values in a column (e.g., changing 'Male' to 'Female' and 'Female' to 'Male' in a gender column) using a single UPDATE statement.
The Naive Pitfall
Running two sequential updates will overwrite your data:
-- DO NOT DO THIS
UPDATE orders SET gender = 'Male' WHERE gender = 'Female';
-- Now all records are 'Male'. The original 'Male' records cannot be distinguished!
UPDATE orders SET gender = 'Female' WHERE gender = 'Male';
-- Now all records have been incorrectly set to 'Female'.
The Correct Approach: Inline CASE Statement
An inline CASE statement evaluates the condition for each row and applies the swap in a single pass.
UPDATE orders
SET gender = CASE
WHEN gender = 'Male' THEN 'Female'
WHEN gender = 'Female' THEN 'Male'
ELSE gender -- Handles NULLs or other values safely
END;
10. The Mathematics of Joins: Duplicates and Nulls
Understanding how joins handle duplicate keys and NULL values is a common way interviewers test your core SQL knowledge.
Scenario A: Joining Tables with Duplicate Keys
Suppose we have two tables with a single column id:
- Table 1 (T1):
[1, 1](Two rows) - Table 2 (T2):
[1, 1, 1](Three rows)
When joining these tables on T1.id = T2.id, the database engine performs a Cartesian product for each matching key group.
Scenario B: Adding Non-Matching Keys
- T1:
[1, 1, 2] - T2:
[1, 1, 1, 3]
1. Inner Join
Only matching keys (1) are returned.
2. Left Join
Returns all matching rows plus unmatched rows from the left table (2), with right-side columns filled with NULL.
3. Right Join
Returns all matching rows plus unmatched rows from the right table (3), with left-side columns filled with NULL.
4. Full Outer Join
Returns all matching rows plus unmatched rows from both tables.
Scenario C: The Behavior of NULL in Joins
In SQL, NULL represents an unknown value. Therefore, the comparison NULL = NULL evaluates to UNKNOWN, not TRUE.
Rule:
NULLvalues never match otherNULLvalues in a standard join.
Let's analyze the following tables:
- T1:
[1, 1, 2, NULL] - T2:
[1, 1, 1, 3, NULL]
1. Inner Join
The NULL values do not match. Only keys 1 match.
2. Left Join
The NULL in T1 is treated as an unmatched row, just like the value 2.
3. Full Outer Join
Both the left NULL and right NULL are treated as independent, unmatched rows.
11. Advanced Analytical Patterns
Year-over-Year (YoY) Growth
Calculating YoY growth requires comparing the current year's sales with the previous year's sales. This is done using the LAG() window function.
WITH YearlySales AS (
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
SUM(sales) AS current_year_sales
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
),
SalesWithLag AS (
SELECT
order_year,
current_year_sales,
LAG(current_year_sales, 1) OVER (ORDER BY order_year) AS previous_year_sales
FROM YearlySales
)
SELECT
order_year,
current_year_sales,
COALESCE(previous_year_sales, current_year_sales) AS previous_year_sales,
ROUND(
((current_year_sales - COALESCE(previous_year_sales, current_year_sales))
/ COALESCE(previous_year_sales, current_year_sales)) * 100,
2
) AS yoy_growth_percent
FROM SalesWithLag;
Rolling and Cumulative Sums
A cumulative sum (running total) calculates the sum of all preceding values up to the current row. A rolling sum calculates the sum over a sliding window of a defined size (e.g., a 3-month rolling window).
-- Cumulative Sum (Running Total)
SELECT
order_year,
sales,
SUM(sales) OVER (ORDER BY order_year) AS cumulative_sales
FROM yearly_sales;
-- Rolling 3-Month Sum (Current Month + 2 Preceding Months)
SELECT
order_month,
sales,
SUM(sales) OVER (
ORDER BY order_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_3_month_sales
FROM monthly_sales;
-- Rolling 3-Month Sum Excl. Current Month (3 Preceding Months to 1 Preceding Month)
SELECT
order_month,
sales,
SUM(sales) OVER (
ORDER BY order_month
ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING
) AS rolling_prev_3_month_sales
FROM monthly_sales;
Pivoting (Rows to Columns)
Pivoting converts row-level data into column-level data. The most portable way to do this across different database systems is using conditional aggregation with CASE WHEN and SUM().
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
SUM(CASE WHEN category = 'Furniture' THEN sales ELSE 0 END) AS furniture_sales,
SUM(CASE WHEN category = 'Office Supplies' THEN sales ELSE 0 END) AS office_supplies_sales,
SUM(CASE WHEN category = 'Technology' THEN sales ELSE 0 END) AS technology_sales
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date);
12. Hard Interview Problems Walkthrough
Problem 1: Workaholic Employees (Meta)
Problem Statement: Identify employees who meet at least one of the following criteria:
- Worked hours/day for days in a week.
- Worked hours/day for days in a week.
The input table user_logins contains emp_id, login_time, and logout_time.
WITH DailyHours AS (
-- Calculate exact decimal hours worked per day
SELECT
emp_id,
CAST(login_time AS DATE) AS work_date,
DATEDIFF(minute, login_time, logout_time) * 1.0 / 60.0 AS hours_worked
FROM user_logins
),
DailyCategorization AS (
-- Categorize each day based on hours worked
SELECT
emp_id,
work_date,
CASE
WHEN hours_worked > 10.0 THEN '10_PLUS'
WHEN hours_worked > 8.0 THEN '8_PLUS'
ELSE 'UNDER_8'
END AS work_category
FROM DailyHours
),
EmployeeCounts AS (
-- Aggregate counts of qualifying days per employee
-- Note: 10_PLUS days also count as 8_PLUS days
SELECT
emp_id,
SUM(CASE WHEN work_category IN ('8_PLUS', '10_PLUS') THEN 1 ELSE 0 END) AS days_over_8,
SUM(CASE WHEN work_category = '10_PLUS' THEN 1 ELSE 0 END) AS days_over_10
FROM DailyCategorization
GROUP BY emp_id
)
-- Apply final criteria filter and output the matched criteria
SELECT
emp_id,
CASE
WHEN days_over_8 >= 3 AND days_over_10 >= 2 THEN 'Both'
WHEN days_over_8 >= 3 THEN '1'
WHEN days_over_10 >= 2 THEN '2'
END AS criteria_satisfied
FROM EmployeeCounts
WHERE days_over_8 >= 3 OR days_over_10 >= 2
ORDER BY emp_id ASC;
Problem 2: Malware Detection (LinkedIn)
Problem Statement: Find the number of malwares detected by each software in its latest run, and the difference in detections between the latest run and the second-to-last run.
- Exclude software that has run fewer than 2 times.
- Exclude software that detected fewer than 10 malwares in its latest run.
WITH RankedRuns AS (
-- Rank runs for each software, with 1 being the latest
SELECT
software_id,
malware_detected,
ROW_NUMBER() OVER (
PARTITION BY software_id
ORDER BY run_date DESC
) AS rn
FROM malware_runs
),
FilteredRuns AS (
-- Keep only the latest and second-to-last runs
SELECT *
FROM RankedRuns
WHERE rn <= 2
),
PivotedRuns AS (
-- Pivot the latest and second-to-last runs into a single row per software
SELECT
software_id,
MAX(CASE WHEN rn = 1 THEN malware_detected END) AS latest_run_count,
MAX(CASE WHEN rn = 2 THEN malware_detected END) AS previous_run_count,
COUNT(1) AS run_count
FROM FilteredRuns
GROUP BY software_id
)
-- Apply filters and calculate the difference
SELECT
software_id,
latest_run_count,
(latest_run_count - previous_run_count) AS diff_to_previous
FROM PivotedRuns
WHERE run_count = 2 -- Ensures the software has run at least twice
AND latest_run_count >= 10; -- Ensures the latest run detected at least 10 malwares
Problem 3: User Session Activity
Problem Statement: Group user activities into sessions. A session is defined as a sequence of activities by a user where the time gap between consecutive events is minutes. If the gap exceeds 30 minutes, it marks the start of a new session.
WITH EventGaps AS (
-- Get the timestamp of the previous event for each user
SELECT
user_id,
event_time,
LAG(event_time, 1, event_time) OVER (
PARTITION BY user_id
ORDER BY event_time
) AS prev_event_time
FROM user_events
),
GapMinutes AS (
-- Calculate the gap in minutes between consecutive events
SELECT
user_id,
event_time,
DATEDIFF(minute, prev_event_time, event_time) AS minutes_since_last
FROM EventGaps
),
SessionFlags AS (
-- Flag the start of a new session (gap > 30 minutes)
SELECT
user_id,
event_time,
CASE
WHEN minutes_since_last > 30 THEN 1
ELSE 0
END AS is_new_session
FROM GapMinutes
),
SessionGroups AS (
-- Generate a unique session ID per user using a cumulative sum of the flags
SELECT
user_id,
event_time,
SUM(is_new_session) OVER (
PARTITION BY user_id
ORDER BY event_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_id
FROM SessionFlags
)
-- Aggregate events by user and session ID
SELECT
user_id,
session_id,
MIN(event_time) AS session_start,
MAX(event_time) AS session_end,
DATEDIFF(minute, MIN(event_time), MAX(event_time)) AS session_duration_minutes,
COUNT(1) AS event_count
FROM SessionGroups
GROUP BY user_id, session_id
ORDER BY user_id, session_start;
Problem 4: Project Budget Forecasting
Problem Statement: Determine if a project's budget is sufficient to cover the salaries of the assigned employees. Employee salaries are annual, and their cost to a project is calculated proportionally based on the number of days they work on it.
WITH ProjectDailyCosts AS (
-- Calculate the total daily salary rate of all employees assigned to each project
SELECT
p.id AS project_id,
p.title AS project_title,
p.budget AS project_budget,
DATEDIFF(day, p.start_date, p.end_date) AS project_duration_days,
SUM(e.salary) * 1.0 / 365.0 AS total_daily_salary_rate
FROM projects p
INNER JOIN project_employees pe
ON p.id = pe.project_id
INNER JOIN employees e
ON pe.emp_id = e.id
GROUP BY p.id, p.title, p.budget, p.start_date, p.end_date
),
ProjectForecast AS (
-- Calculate the total projected cost for each project
SELECT
project_title,
project_budget,
(total_daily_salary_rate * project_duration_days) AS projected_cost
FROM ProjectDailyCosts
)
-- Compare the projected cost against the budget
SELECT
project_title,
project_budget,
ROUND(project_cost, 2) AS projected_cost,
CASE
WHEN projected_cost > project_budget THEN 'Over Budget'
ELSE 'Within Budget'
END AS budget_status
FROM ProjectForecast
ORDER BY project_title;
Problem 5: Compounded Promotions via Recursive CTE
Problem Statement: Calculate the current salary of employees after applying multiple sequential promotions. Each promotion increases the employee's salary by a specified percentage. Promotions must be applied sequentially in chronological order, compounding the salary at each step.
Initial Salary: 50,000
Promotion 1 (10%): 50,000 * 1.10 = 55,000
Promotion 2 (20%): 55,000 * 1.20 = 66,000
WITH RankedPromotions AS (
-- Assign a sequential ID to each promotion per employee
SELECT
emp_id,
percent_increase,
ROW_NUMBER() OVER (
PARTITION BY emp_id
ORDER BY promotion_date ASC
) AS promo_sequence_id
FROM promotions
),
CompoundedSalaryCTE AS (
-- Anchor Member: Initialize with the joining salary (promo_sequence_id = 0)
SELECT
id AS emp_id,
name AS emp_name,
joining_salary AS initial_salary,
CAST(joining_salary AS DECIMAL(18, 2)) AS current_salary,
0 AS promo_sequence_id
FROM employees
UNION ALL
-- Recursive Member: Join with the next promotion in sequence and compound the salary
SELECT
r.emp_id,
r.emp_name,
r.initial_salary,
CAST(r.current_salary * (1.0 + (p.percent_increase / 100.0)) AS DECIMAL(18, 2)) AS current_salary,
p.promo_sequence_id
FROM CompoundedSalaryCTE r
INNER JOIN RankedPromotions p
ON r.emp_id = p.emp_id
AND r.promo_sequence_id + 1 = p.promo_sequence_id
),
LatestSalarySequence AS (
-- Identify the final state (highest sequence ID) for each employee
SELECT
emp_id,
emp_name,
initial_salary,
current_salary,
ROW_NUMBER() OVER (
PARTITION BY emp_id
ORDER BY promo_sequence_id DESC
) AS final_state_rn
FROM CompoundedSalaryCTE
)
-- Select the final compounded salary for each employee
SELECT
emp_id,
emp_name,
initial_salary,
ROUND(current_salary, 1) AS current_salary
FROM LatestSalarySequence
WHERE final_state_rn = 1
ORDER BY emp_id;
Summary and Key Takeaways
- Duplicate Detection: Always use
GROUP BYcombined withHAVING COUNT(1) > 1to find duplicate records. - De-duplication: Use a CTE with
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)to assign sequential IDs to duplicate groups, then delete rows where the row number is greater than 1. - Union Operators: Use
UNION ALLby default for performance. Only useUNIONwhen you explicitly need to remove duplicate rows across the combined datasets. - Ranking Functions:
ROW_NUMBER()assigns unique, sequential numbers.RANK()allows ties but skips subsequent numbers.DENSE_RANK()allows ties and does not skip numbers.
- Anti-Joins: Use a
LEFT JOINcombined with aWHERE right_table.key IS NULLfilter instead ofNOT INto avoid issues withNULLvalues. - Self-Joins: Use self-joins to query hierarchical relationships stored in a single table, such as employee-to-manager mappings.
- Value Swapping: Use a single
UPDATEstatement with aCASE WHENblock to swap values in a column without losing data. - Join Mathematics: Standard joins do not match
NULLvalues. In joins with duplicate keys, the output row count for a key is the product of its occurrences in both tables. - Pivoting: Use conditional aggregation (
SUM(CASE WHEN ... THEN ... ELSE 0 END)) to pivot row-level data into columns in a portable, database-agnostic way. - Recursive CTEs: Use recursive CTEs to solve complex hierarchical problems or compound sequential calculations, such as calculating compounded salary increases.