What is the difference between RANK() and DENSE_RANK() in SQL?

Both rank ordered rows. RANK skips subsequent numbers when duplicate values occur (1, 2, 2, 4), while DENSE_RANK maintains continuous numbers (1, 2, 2, 3).

Top 50 SQL Interview Queries & Questions for 2026 (With Solutions)

Q: What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before aggregation, while HAVING filters grouped aggregated buckets after the GROUP BY clause.

1. Core SQL Fundamentals & Logical Query Execution Order

Most developers write SQL starting from SELECT, but SQL database engines process clauses in a very specific logical order. Understanding this order is the #1 trick to answering tricky interview questions about aliases in WHERE clauses or aggregate filtering.

-- Logical Execution Order of SQL Clauses:
1. FROM & JOIN (Identify base tables & Cartesian product)
2. WHERE (Row-level filtering before aggregation)
3. GROUP BY (Group remaining rows into buckets)
4. HAVING (Filter grouped aggregate buckets)
5. SELECT (Evaluate expressions, column projections & window funcs)
6. DISTINCT (Deduplicate projected rows)
7. ORDER BY (Sort resulting rows)
8. LIMIT / OFFSET (Slice returned row range)

Why can't you use a SELECT alias in a WHERE clause?
Because WHERE runs at step #2, before SELECT (step #5) has projected and assigned the alias!

2. Most Frequently Asked Practical SQL Queries (With Code)

Q1. Find the Nth highest salary in an Employee table without skipping ranks.

Using DENSE_RANK() inside a CTE handles ties gracefully without missing consecutive ranks.

WITH RankedSalaries AS ( SELECT id, name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM Employee
)
SELECT salary FROM RankedSalaries WHERE rnk = 2; -- Change 2 to N

Q2. Find duplicate email addresses in a Users table and delete all duplicates keeping the lowest ID.

-- 1. Find duplicates:
SELECT email, COUNT(*) FROM Users GROUP BY email HAVING COUNT(*) > 1; -- 2. Delete duplicates keeping lowest id:
DELETE FROM Users WHERE id NOT IN ( SELECT MIN(id) FROM (SELECT * FROM Users) as temp GROUP BY email
);

Q3. Write a query to calculate month-over-month revenue growth percentage.

WITH MonthlySales AS ( SELECT DATE_TRUNC('month', order_date) as order_month, SUM(amount) as current_month_revenue FROM Orders GROUP BY DATE_TRUNC('month', order_date)
)
SELECT order_month, current_month_revenue, LAG(current_month_revenue, 1) OVER (ORDER BY order_month) as prev_month_revenue, ROUND( ((current_month_revenue - LAG(current_month_revenue, 1) OVER (ORDER BY order_month)) / LAG(current_month_revenue, 1) OVER (ORDER BY order_month)) * 100, 2 ) as mom_growth_pct
FROM MonthlySales;

3. Window Functions: RANK vs DENSE_RANK vs ROW_NUMBER

Window functions perform calculations across a set of table rows related to the current row without collapsing rows like GROUP BY.

Salary	ROW_NUMBER()	RANK()	DENSE_RANK()
$100,000	1	1	1
$90,000	2	2	2
$90,000	3	2	2
$80,000	4	4 (skips 3)	3 (continuous)

4. Indexing, EXPLAIN Plans & Query Optimization

Writing queries is only half the battle; ensuring queries run in milliseconds on millions of records is what senior engineering interviewers evaluate.

Clustered Index: Dictates the physical storage order of data rows on disk. Only one per table (usually the Primary Key).
Non-Clustered Index: A separate B+Tree data structure holding sorted indexed columns with pointers back to the actual data rows.
Covering Index: An index containing all columns requested by a SELECT query, eliminating disk lookup hops (Heap/Clustered lookups).
SARGable Queries: Ensure filtering conditions can leverage indexes (e.g., avoid WHERE YEAR(created_at) = 2026; instead use WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01').

5. Transactions, ACID Properties & Concurrency Anomalies

ACID Properties:

Atomicity

All-or-nothing execution. If any operation fails, the entire transaction rolls back.

Consistency

Transactions transition database states strictly adhering to constraints and integrity rules.

Isolation

Concurrent transactions execute independently without interfering with in-flight states.

Durability

Committed updates persist permanently in Write-Ahead Logs (WAL), surviving system crashes.

6. Normalization (1NF to BCNF) vs Denormalization

Normalization minimizes data redundancy and eliminates insertion, update, and deletion anomalies:

Normalization Normal Forms

1NF: Atomic values only; no repeating column groups or array lists.
2NF: In 1NF + no partial dependency on composite candidate keys.
3NF: In 2NF + no transitive dependencies (non-prime attributes depend ONLY on candidate keys).
BCNF: For every functional dependency X -> Y, X must be a super key.

When to Denormalize?

In high-read analytical databases (OLAP / Data Warehouses), denormalization avoids costly multi-table joins and drastically speeds up dashboard queries at the cost of duplicate storage.

Ready to Practice SQL Technical Rounds?

Practice voice-based SQL mock interviews with Sophia AI. Speak your queries and explanation logic out loud and receive instant grading on syntax, normalization, and optimization.

Start Free SQL Mock Interview Data Engineering Mock

7. Frequently Asked SQL Interview Questions

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before any grouping or aggregation takes place. HAVING filters grouped summary rows after the GROUP BY clause is evaluated and supports aggregate functions like SUM(), COUNT(), or AVG().

What is the difference between DELETE, TRUNCATE, and DROP?

DELETE is a DML command that removes rows one-by-one, logs each deletion, and supports WHERE clauses and rollback. TRUNCATE is a DDL command that deallocates entire data pages, resetting identity counters much faster without row-by-row logging. DROP removes the entire table definition and its schema from the database completely.

Key Takeaways — SQL Interviews in 2026