SQL Query Optimization & Indexing Interview Questions (2026)
SQL optimization is a staple of technical interviews across FAANG and high-growth startups. Interviewers present slow queries and ask you to dissect EXPLAIN ANALYZE plans, identify sequential scans, choose appropriate indexing strategies (B-Tree, Hash, GIN, BRIN), eliminate non-sargable predicates, and optimize complex subqueries and CTEs.
Tested Core Concepts & Weightage
Distribution within this topicUnlocked FAANG-Calibrated Answer Rubric (Top 3 Tested Questions)
- •Rigorous explanation of data distribution, memory boundaries, and execution bottlenecks
- •Quantified improvements (e.g. 60% shuffle volume reduction, predictable SLA under 10x spikes)
- •Proactive trade-off analysis between latency, storage overhead, and operational complexity
Complete SQL Query Optimization & Indexing Question Vault (366 Questions)
Showing 15 of 366Demonstrate the difference between DENSE_RANK() and RANK()
**RANK()**: Same rank for ties; skips subsequent ranks (e.g., 1, 2, 2, 4, 5). **DENSE_RANK()**: Same rank for ties; no gaps (e.g., 1, 2, 2, 3, 4). **Why it matters**: RANK preserves "position" semantics (e.g., 4th place); DENSE_RANK gives consecutive integers useful for filtering (e.g., TOP 10). **Example**: `SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rk FROM employee`....
Explain the differences between Repartition and Coalesce. When would you use each?
**Repartition(n)**: Full shuffle; creates exactly n partitions. Can increase or decrease. **Coalesce(n)**: Merges partitions without full shuffle; only decreases. **Why it matters**: Shuffle is expensive—network and disk I/O. Coalesce avoids shuffle when reducing partitions by merging within existing partitions. **When Repartition**: Increasing partitions, fixing skew (repartition by key), or before a join to align partition counts....
Explain the differences between a Data Lake and a Data Warehouse.
A Data Lake is a centralized repository storing vast amounts of raw, multi structured data (structured, semi structured, unstructured) in its native format, applying schema on read for flexibility. A…
What is the difference between partitioning and bucketing in Spark, and when would you use bucketing?
Partitioning physically divides data into separate directories based on column values, enabling Spark to skip irrelevant data during queries (partition pruning). Bucketing, conversely, organizes data…
Can you explain the difference between OLTP and OLAP?
OLTP (Online Transaction Processing) systems are optimized for many concurrent, small, atomic transactions (inserts, updates, deletes), ensuring data integrity and consistency. OLAP (Online Analytical…
Describe a time when you had to optimize a slow SQL query. What steps did you take?
**Situation**: A critical exec report was timing out at 30+ minutes; SLA was 5 minutes. **Task**: Diagnose and fix without changing business logic. **Action**: I ran EXPLAIN (ANALYZE) and found: (1) missing index on join key causing full table scan, (2) cross join that could be inner join with a filter, (3) filter in HAVING that could move to WHERE to reduce rows early, (4) unnecessary ORDER BY on a subquery....
Explain the concept of ACID properties in the context of databases.
ACID properties (Atomicity, Consistency, Isolation, Durability) are a set of principles that guarantee reliable transaction processing in databases, ensuring data integrity and reliability even amidst…
Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right, with NULLs for non matches. RIGHT JOIN is symmetrical,…
What is a Common Table Expression (CTE), and when would you use it?
A Common Table Expression (CTE) is a named, temporary result set defined within a WITH clause, which can then be referenced multiple times within the main SQL query. It acts like a temporary view that…
Describe a scenario where partitioning and bucketing would improve query performance.
Situation: An events table with billions of rows serving time-range and user-level analytics. Task: Achieve sub-second query latency while controlling storage and compute costs. Why Partitioning: Partition pruning at read time eliminates entire data scans—a query filtering by date_range only touches relevant partition dirs. This reduces I/O by orders of magnitude (e.g., 365 partitions → scan 1 vs all)....
Explain Fact and Dimension Tables with examples.
Architecture: Star schema centralizes measurable events in fact tables; dimensions provide semantic context. Why this design: Facts are append-heavy and grow unbounded; dimensions are smaller and change slowly. Separating them optimizes for different access patterns. Fact grain defines the entire schema—get it wrong and joins become wrong. Example: sales_fact (quantity, revenue, date_key, product_key, customer_key) at grain one row per transaction....
Explain the types of triggers in ADF, including schedule, tumbling window, and event-based triggers.
Schedule: Fixed cadence (cron, every N mins). Predictable batch windows; simple ops. Tumbling window: Fixed non-overlapping intervals; fires once per window. Ideal for idempotent, exactly-once semantics—no overlap means no double-processing. Event-based: Fires on blob created, queue message, etc. Enables near real-time pipelines....
How do you remove duplicate rows in BigQuery?
Approach: Use ROW_NUMBER() OVER (PARTITION BY dedup_keys ORDER BY tie_breaker) to define which row to keep; filter rn = 1. Preferred pattern: CREATE OR REPLACE TABLE ... AS SELECT * EXCEPT(rn) FROM (SELECT *, ROW_NUMBER() OVER (...) AS rn ...) WHERE rn = 1. Why CREATE OR REPLACE over DELETE: BigQuery is columnar; DELETE is a rewrite under the hood. For large tables, CREATE OR REPLACE is a single scan+write vs DELETE's read-modify-write....
Joins and window functions - INNER, LEFT, RIGHT, FULL OUTER, ROW_NUMBER(), RANK(), DENSE_RANK()
Joins: INNER = intersection only; LEFT = all left + matching right (NULL fill); RIGHT = mirror of LEFT; FULL OUTER = union of both. Why it matters: Join choice affects result cardinality and NULL handling—wrong join = wrong business logic (e.g., LEFT to preserve all customers even without orders). Window functions: ROW_NUMBER() = unique rank 1,2,3; RANK() = ties same rank, gaps after; DENSE_RANK() = ties same rank, no gaps....
How does Spark's Catalyst Optimizer work? Explain its stages.
Spark's Catalyst Optimizer is a sophisticated, multi stage query optimizer that translates declarative DataFrame/Spark SQL queries into efficient execution plans. It employs a hybrid approach,…
Stop Guessing If Your Answers Clear the Bar at SQL Query Optimization & Indexing
DataEngPrep’s AI evaluates your real answers against strict FAANG hire rubrics. Check accuracy, optimize shuffle partitions, detect edge case omissions, and receive an instant 9/10 rewrite before stepping into your real interview loop.
Related Technical Topics & Competencies
Frequently paired together during multi-round senior data engineering loops.
Frequently Asked Questions About SQL Query Optimization & Indexing Interviews
What makes a SQL query predicate non-sargable?▼
A predicate is non-sargable (Search Argument Able) when an operation or function is applied directly to the indexed column (e.g. `WHERE YEAR(created_at) = 2026` or `WHERE LOWER(email) = '...'`). The database engine cannot use the B-Tree index and is forced to perform an expensive full table scan. Rewriting to a range query (e.g. `WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'`) restores sargability.
When does a database engine choose a Hash Join over a Nested Loop or Merge Join?▼
Nested Loop joins are chosen for small tables or when one side is indexed. Merge Joins are preferred when both inputs are already sorted on the join key. Hash Joins are chosen for larger, unsorted datasets: the engine builds an in-memory hash table of the smaller relation, then streams the larger relation to probe for matches.
Why do CTEs (Common Table Expressions) sometimes run slower than subqueries in PostgreSQL?▼
Prior to PostgreSQL 12, CTEs acted as optimization fences — the query optimizer materialized the CTE completely in memory or temp disk before proceeding, preventing predicate pushdown. While newer versions inline simple CTEs, complex recursive CTEs or explicit `MATERIALIZED` flags can still hinder optimization.
How can I practice SQL performance tuning questions?▼
DataEngPrep's query optimization collection provides real query plans, indexing dilemmas, and schema designs with AI-guided rubrics to help you explain trade-offs clearly in live interviews.