Mastering SQL for Technical Interviews: Complete Zero-to-Advanced Interview Preparation Handbook (2026)

1. Introduction & Logical SQL Query Execution Order

In technical interviews for Data Engineering, Backend Development, and Full-Stack Engineering, SQL is one of the most rigorously tested skills. Many developers write SQL intuitively based on syntax, but fail interview questions because they do not understand how the database engine executes queries under the hood.

While you write SQL starting with SELECT, the SQL engine evaluates clauses in a strict mathematical order:

┌─────────────────────────────────────────────────────────────────────────────┐
│                       LOGICAL SQL EXECUTION ORDER                            │
├─────────────────────────────────────────────────────────────────────────────┤
│ 1. FROM        ──> Identify source tables and virtual table buffers         │
│ 2. ON          ──> Evaluate join conditions for each candidate row pair     │
│ 3. JOIN        ──> Materialize Joined Table (INNER, LEFT, RIGHT, FULL)      │
│ 4. WHERE       ──> Filter individual rows BEFORE grouping                   │
│ 5. GROUP BY    ──> Collapse rows into distinct group partitions             │
│ 6. HAVING      ──> Filter aggregated group metrics (AFTER grouping)         │
│ 7. SELECT      ──> Compute output expressions, subqueries, column aliases   │
│ 8. DISTINCT    ──> Deduplicate resulting rows                               │
│ 9. ORDER BY    ──> Sort final records (can use SELECT aliases)              │
│ 10. LIMIT/OFF  ──> Slice row offset window for client response              │
└─────────────────────────────────────────────────────────────────────────────┘

2. SQL Joins Masterclass: Mechanics & Anti-Patterns

Relational algebra combines datasets based on predicate matching. Understanding the nuances between join types is essential for both performance and data correctness.

The Anti-Join Pattern: Finding Missing Records

-- Pattern 1: LEFT JOIN with IS NULL (Highly efficient with indexes)
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Pattern 2: NOT EXISTS (Best optimizer performance with subquery)
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- ⚠️ WARNING: 'NOT IN' FAILS SILENTLY IF SUBQUERY CONTAINS NULL VALUES!
-- If any o.customer_id is NULL, 'NOT IN' evaluates to UNKNOWN and returns 0 rows!

3. Window Functions: The #1 Most Tested SQL Interview Topic

Unlike GROUP BY which collapses multiple rows into a single aggregated summary row, Window Functions compute values across a sliding partition of rows while preserving each individual row's identity.

1. Ranking Functions: ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
    employee_id,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

2. Value & Offset Functions: LEAD, LAG, FIRST_VALUE

-- Month-Over-Month (MoM) Revenue Growth Calculation
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS order_month,
        SUM(order_total) AS total_revenue
    FROM orders
    GROUP BY 1
)
SELECT
    order_month,
    total_revenue,
    LAG(total_revenue, 1) OVER (ORDER BY order_month) AS previous_month_revenue,
    ROUND(
        (total_revenue - LAG(total_revenue, 1) OVER (ORDER BY order_month))::numeric
        / NULLIF(LAG(total_revenue, 1) OVER (ORDER BY order_month), 0) * 100.0, 2
    ) AS mom_growth_pct
FROM monthly_revenue;

3. Running Totals & Moving Averages

-- Cumulative Running Total per Customer
SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend,
    AVG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_3_order_avg
FROM customer_orders;

4. Common Table Expressions (CTEs) & Recursive SQL

CTEs (WITH ... AS) improve query readability and modularity. Recursive CTEs solve graph and hierarchical tree problems (such as corporate reporting structures or bill of materials).

-- Organizational Hierarchy: Find all subordinates under Manager (CEO ID = 1)
WITH RECURSIVE OrgHierarchy AS (
    -- 1. Anchor Member (Base Case: CEO)
    SELECT
        employee_id,
        first_name,
        manager_id,
        1 AS org_level,
        first_name::text AS path
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    -- 2. Recursive Member (Join back to CTE)
    SELECT
        e.employee_id,
        e.first_name,
        e.manager_id,
        h.org_level + 1,
        h.path || ' -> ' || e.first_name
    FROM employees e
    INNER JOIN OrgHierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM OrgHierarchy ORDER BY org_level, employee_id;

5. Database Indexing & Query Optimization Internals

Senior database interviews test your understanding of hardware I/O, B-Tree indexes, and why queries run slowly in production.

1. B-Tree Index Architecture

• Root & Branch Nodes: Store key pointers to navigate large datasets in O(log N) time.
• Leaf Nodes: Linked double-ended lists containing physical tuple IDs (Heap pointers) or clustered row data.
• Range Scans: B-Trees excel at =, <, >, BETWEEN, and ORDER BY.

2. The Leftmost Prefix Rule on Composite Indexes

If you create a composite index on CREATE INDEX idx_user_status_date ON users(country_code, status, created_at);

• WHERE country_code = 'US' AND status = 'ACTIVE' → Uses Index (Full Speed)
• WHERE country_code = 'US' → Uses Index (Leading column)
• WHERE status = 'ACTIVE' → Full Table Scan! (Leftmost column missing)

3. SARGability (Search Argument Able)

Wrapping indexed columns in functions disables index lookup and forces full table scans:

-- ❌ BAD: Non-SARGable (Forces Full Table Scan on 50 Million rows)
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- ✅ GOOD: SARGable (Utilizes B-Tree Range Scan Index)
SELECT * FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

-- ❌ BAD: Expression on Column
SELECT * FROM products WHERE price * 1.18 > 1000;

-- ✅ GOOD: Expression moved to Constant literal
SELECT * FROM products WHERE price > (1000 / 1.18);

6. ACID Transactions, Concurrency & Isolation Levels

7. Top 10 High-Frequency SQL Coding Interview Problems

Problem 1: Find the Nth Highest Salary

-- Solution 1: Using DENSE_RANK (Handles duplicate ties gracefully)
WITH RankedSalaries AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT DISTINCT salary
FROM RankedSalaries
WHERE rnk = 2; -- Change '2' to Nth

-- Solution 2: Using LIMIT / OFFSET (Simple single value)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- For 2nd highest, OFFSET = N - 1

Problem 2: Department Top 3 Salaries (LeetCode #185)

WITH RankedDeptSalaries AS (
    SELECT
        d.name AS Department,
        e.name AS Employee,
        e.salary AS Salary,
        DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rnk
    FROM employees e
    INNER JOIN departments d ON e.department_id = d.id
)
SELECT Department, Employee, Salary
FROM RankedDeptSalaries
WHERE rnk <= 3;

Problem 3: Find Consecutive Active Logins (3 or More Days)

WITH DateGrouped AS (
    SELECT
        user_id,
        login_date,
        login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
    FROM user_logins
    GROUP BY user_id, login_date -- deduplicate same day logins
)
SELECT
    user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*) AS consecutive_days
FROM DateGrouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Problem 4: Delete Duplicate Rows While Keeping the Smallest ID

-- Solution 1: Using DELETE with Self-Join
DELETE FROM customers
WHERE id IN (
    SELECT c1.id
    FROM customers c1
    INNER JOIN customers c2 ON c1.email = c2.email AND c1.id > c2.id
);

-- Solution 2: Using CTE with ROW_NUMBER (PostgreSQL / SQL Server)
WITH Duplicates AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM Duplicates WHERE rn > 1);

Problem 5: Employees Earning More Than Their Immediate Managers

SELECT
    e.name AS Employee,
    e.salary AS EmployeeSalary,
    m.name AS Manager,
    m.salary AS ManagerSalary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

8. Complete SQL Interview Rapid Recall Cheatsheet

• ✓ Execution Order: FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
• ✓ WHERE vs HAVING: WHERE filters raw records before grouping; HAVING filters aggregated calculations ($SUM, COUNT, AVG$).
• ✓ COUNT(*) vs COUNT(col): COUNT(*) counts all rows including NULLs; COUNT(col) counts only non-NULL rows.
• ✓ NULL Comparisons: Always use IS NULL or IS NOT NULL; col = NULL evaluates to UNKNOWN and never returns true.
• ✓ UNION vs UNION ALL: UNION performs an expensive distinct sorting sort to remove duplicates; UNION ALL appends rows immediately in $O(1)$ time.
• ✓ DENSE_RANK vs RANK: DENSE_RANK leaves no numerical gaps after ties ($1, 2, 2, 3$); RANK skips numbers ($1, 2, 2, 4$).
• ✓ COALESCE: COALESCE(val1, val2, default) returns the first non-NULL expression from left to right.
• ✓ NULLIF: NULLIF(val, 0) converts 0 to NULL to prevent division by zero runtime crashes (division by zero error).
• ✓ TRUNCATE vs DELETE vs DROP: DELETE is DML (row by row, logged, rollbackable); TRUNCATE is DDL (deallocates pages, instant, resets auto-increment); DROP destroys table structure completely.
• ✓ B-Tree vs Hash Index: B-Tree handles range queries ($>, <, BETWEEN$); Hash index only supports exact equality ($=$) lookups.