SQL Interview Practice

Q1 EASY · Aggregation

Find the second highest salary

Given an Employee table, write a query to return the second highest salary. If there is no second highest salary, return NULL.

MAX Subquery LIMIT Window Functions

Q2 EASY · Self Join

Employees earning more than their manager

Given an Employee table where ManagerID points to another EmployeeID in the same table, find all employees who earn more than their direct manager.

Self Join INNER JOIN Hierarchy

Q3 EASY · Joins

JOIN types — INNER, LEFT, RIGHT, FULL OUTER

Two tables: Orders and Customers. Demonstrate each JOIN type. Note: sql.js does not support RIGHT JOIN or FULL OUTER JOIN directly — use UNION simulations.

INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN

Q4 EASY · Aggregation

GROUP BY with HAVING

Find products with total sales greater than 1000. Demonstrate the difference between WHERE (row filter) and HAVING (group filter).

GROUP BY HAVING SUM COUNT

Q5 MEDIUM · Window Functions

Window functions — ROW_NUMBER, RANK, DENSE_RANK

Show three ways to rank employees by salary within each department. Notice how they behave differently when there are ties.

ROW_NUMBER RANK DENSE_RANK PARTITION BY

Q6 MEDIUM · Window Functions

Running total with SUM OVER

Compute a cumulative running total of sales by date.

SUM OVER Cumulative Window Frames LAG

Q7 EASY · Set Operations

UNION vs UNION ALL

Combine sales from 2023 and 2024. Show the difference between UNION (dedup) and UNION ALL (keep all).

UNION UNION ALL Deduplication

Q8 MEDIUM · Data Quality

Find duplicate records

Find customers with duplicate email addresses, then show how to keep only the most recent.

GROUP BY HAVING ROW_NUMBER CTEs

Q9 MEDIUM · Window Functions

Top N customers by revenue

Find the top 5 customers by total revenue along with their rank. Practice the multi-step approach with CTEs.

RANK CTE Aggregation

Q10 HARD · Recursive CTEs

Recursive CTE — employee hierarchy

Find all direct and indirect reports for a manager. Standard hierarchy traversal — a common Hard interview problem.

Recursive CTE Hierarchy WITH RECURSIVE

Q11 HARD · Window Functions

Find gaps in sequential data

Given order IDs 1, 2, 3, 5, 6, 8, 9 — identify the missing IDs (4 and 7). Useful for finding deleted records or audit gaps.

LEAD ROW_NUMBER Gap detection

Q12 HARD · Window Functions

Month-over-month growth %

Compute month-over-month sales growth as a percentage. Standard BI / analytics interview question.

LAG CTE Growth calculation

Q13 EASY · Filtering

WHERE vs HAVING

Demonstrate the execution order and difference between WHERE (row filter, before grouping) and HAVING (group filter, after grouping).

WHERE HAVING Execution order

Q14 HARD · Data Quality

Fact table validation queries

Run a battery of data quality checks on a fact table — NULL foreign keys, orphaned rows, negative amounts, future dates.

UNION ALL Validation Data quality checks