SQL Roadmap for Job Interviews: From Basic Queries to Window Functions

The biggest point of confusion for beginners is that SQL queries are NOT executed in the syntactic order in which they are written. You write `SELECT` first, but the database engine evaluates `SELECT` near the very end of its processing pipeline.

The 6-Step Engine Execution Sequence:

1. `FROM` & `JOIN`: The database identifies the target tables, processes `ON` join conditions, and constructs the unified virtual working dataset.

2. `WHERE`: Filters individual rows based on boolean predicates BEFORE any grouping occurs. (Note: Aggregate functions like `COUNT()` or `AVG()` are illegal in `WHERE` clauses).

3. `GROUP BY`: Collapses the filtered rows into distinct bucket groups based on shared column values.

4. `HAVING`: Filters grouped summary rows based on aggregate condition checks (e.g., `HAVING COUNT(order_id) > 5`).

5. `SELECT` & Window Functions: Evaluates expressions, computes analytical window functions, and projects the requested columns (and assigns column aliases).

6. `ORDER BY` & `LIMIT` / `OFFSET`: Sorts the final projected dataset and slices the requested row window.

Why This Matters in Interviews: When an interviewer asks, "Why can't I write `WHERE total_sales > 1000` when `total_sales` is defined as `SUM(price) AS total_sales` in the SELECT clause?", you can immediately explain that `WHERE` executes in Step 2, while the alias `total_sales` is not created until Step 5.

• Memorize the 6-step logical execution order: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
• Understand why aggregate functions cannot be placed in the WHERE clause
• Explain why column aliases created in SELECT cannot be referenced in WHERE or GROUP BY
• Trace query logic step-by-step before writing raw syntax

Relational database interviews heavily test your understanding of set theory and how table relationships affect output row cardinality.

1. `INNER JOIN`: Returns only the rows where there is a matching key in both tables. Any record without a foreign-key match in the right table is omitted.

2. `LEFT JOIN` (Left Outer Join): Returns all rows from the left table, plus matched columns from the right table. If no match exists, the right table columns are populated with `NULL`.

Interview Pattern - Finding Missing Records: To find customers who have never placed an order, use a `LEFT JOIN` on orders and filter `WHERE orders.customer_id IS NULL`. This is often faster and cleaner than writing `WHERE id NOT IN (SELECT customer_id FROM orders)`.

3. `SELF JOIN`: Joining a table to itself using distinct aliases (e.g., `FROM employees e JOIN employees m ON e.manager_id = m.emp_id`). Essential for hierarchical organizational charts and sequential event tracking.

4. `CROSS JOIN`: Computes the Cartesian product of two tables (M x N rows). Used when generating grid matrices or combining all dates with all product SKUs.

Example Query & Solution (Nth Highest Salary Problem): In technical interviews, candidates are often asked: "Find the 2nd highest distinct salary across all employees." While `LIMIT 1 OFFSET 1` works in simple cases, it fails when duplicate top salaries exist. The robust, production-grade approach uses `DENSE_RANK()`: `WITH RankedSalaries AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank_num FROM employees) SELECT salary FROM RankedSalaries WHERE rank_num = 2 LIMIT 1;`. This guarantees the exact 2nd unique salary value regardless of duplicate ties.

• Distinguish between INNER, LEFT, RIGHT, FULL, and CROSS joins
• Use `LEFT JOIN ... WHERE right.key IS NULL` for anti-join detection
• Master SELF JOIN queries for manager-employee hierarchy modeling
• Always specify table aliases to prevent ambiguous column reference errors

Aggregation queries test whether you can summarize operational data into business intelligence metrics.

The Core Aggregate Functions: `COUNT(column)` (counts non-null values), `COUNT(*)` (counts all rows including nulls), `SUM()`, `AVG()`, `MIN()`, and `MAX()`.

Rule of GROUP BY: Every column listed in your `SELECT` statement that is NOT enclosed within an aggregate function MUST be included in the `GROUP BY` clause. Violating this rule triggers syntax errors in PostgreSQL, SQL Server, and modern MySQL.

WHERE vs HAVING Contrast in Interviews:

- `WHERE` filters raw base records before aggregation takes place.

- `HAVING` filters aggregated summaries after `GROUP BY` has collapsed rows.

Example Query: "Find all departments with more than 5 engineers where the average salary exceeds ₹15 LPA":

`SELECT department, AVG(salary) AS avg_sal, COUNT(id) AS emp_count FROM employees WHERE role = 'Engineer' GROUP BY department HAVING COUNT(id) > 5 AND AVG(salary) > 1500000;`

• Include all non-aggregated SELECT columns in the GROUP BY clause
• Use WHERE for row-level filtering and HAVING for group-level threshold checks
• Understand the difference between COUNT(*) and COUNT(column_name) regarding NULL values
• Practice combining multiple aggregate functions in a single query

Window functions allow you to perform calculations across a set of table rows that are related to the current row, without collapsing the rows into a single summary output like `GROUP BY` does.

The Anatomy of a Window Function: `FUNCTION() OVER (PARTITION BY department ORDER BY salary DESC)`

1. `ROW_NUMBER()`: Assigns a unique sequential integer (1, 2, 3...) to each row within the partition, regardless of duplicate values.

2. `RANK()`: Assigns ranking with gaps for duplicate ties (1, 2, 2, 4...).

3. `DENSE_RANK()`: Assigns ranking without gaps for duplicate ties (1, 2, 2, 3...). The standard solution for finding the Nth highest salary per department.

4. `LEAD()` & `LAG()`: Accesses data from subsequent or preceding rows within the partition without requiring a self-join. Indispensable for calculating day-over-day growth percentages or user session inactivity durations.

Common Table Expressions (CTEs): Using the `WITH cte_name AS (...)` syntax replaces deeply nested subqueries with readable, top-to-bottom modular logic that interviewers can review easily.

Illustrative Performance Optimization (Composite Index Optimization): Consider a query filtering `WHERE status = 'ACTIVE' AND created_at >= '2026-01-01' ORDER BY created_at DESC`. An index created solely on `created_at` forces the database engine to scan all rows matching the date range and filter active statuses in memory. Creating a composite B-Tree index on `(status, created_at)` allows the query planner to perform an exact index seek directly to the active partition and scan pre-sorted dates, converting a costly table scan into an efficient index range scan.

• Master the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()
• Use LEAD() and LAG() for time-series and sequential event comparisons
• Write modular multi-step queries using Common Table Expressions (`WITH` clauses)
• Explain the concept of PARTITION BY vs GROUP BY to technical interviewers

In engineering interviews, after you write a working SQL query, the interviewer will often ask: "This query works on 1,000 rows, but how will it perform on 10 million rows, and how would you optimize it?"

Key Optimization Principles:

1. B-Tree Indexes: Creating an index on frequently filtered columns (`CREATE INDEX idx_user_email ON users(email);`) reduces lookup time complexity from an O(N) sequential full-table scan to an O(log N) tree traversal.

2. Composite Indexes & Leftmost Prefix Rule: An index created on `(department_id, salary)` accelerates queries filtering by department or by department and salary, but cannot be used for queries filtering solely by salary.

3. Avoiding Functions on Indexed Columns: Writing `WHERE YEAR(created_at) = 2026` forces the database to evaluate the function on every row, disabling index usage. Write `WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'` instead to maintain index seek efficiency.

4. The `EXPLAIN ANALYZE` Tool: Use `EXPLAIN` to inspect query execution plans and detect costly Sequential Scans and temporary disk sorts.

Review how to integrate backend database projects into your resume in our guide on /guides/best-resume-projects-for-software-freshers.

Explore backend and data engineering openings in our /jobs/category/backend-developer section and log your application progress with our /tracker.

• Explain how B-Tree indexes convert O(N) table scans into O(log N) index lookups
• Follow the leftmost prefix rule when designing multi-column composite indexes
• Avoid wrapping indexed columns in functions within WHERE clauses
• Use EXPLAIN query plans to identify full table scans and expensive joins

CampusToCareer is an independent career-readiness platform helping freshers find curated jobs, prepare for interviews, and build placement-ready profiles. This guide is published as an educational resource for student job seekers.