Interview Prep Guide

SQL Production Analysis and Performance Questions

Applied SQL questions on query plans, indexes, transactions, data correctness, analytical patterns, and production incidents.

Applied SQL Questions

  1. How do you prevent joins from accidentally multiplying rows?

    Define the grain of each input, test key uniqueness, aggregate before joining when needed, and reconcile counts and totals.

  2. How do window frames change a running-total calculation?

    The ordering and frame determine which peer and preceding rows participate; explicit ROWS frames avoid surprising RANGE behavior.

  3. When is a common table expression useful, and when can it hurt?

    CTEs clarify staged logic and recursion, but materialization or repeated work depends on the database and plan.

Advanced SQL Questions

  1. How do you read an execution plan?

    Follow row estimates and actuals through scans, joins, sorts, and aggregates to locate expensive or misestimated work.

  2. How do composite index column order and covering affect queries?

    Leading columns determine useful seek patterns, while included data can avoid lookups at the cost of write and storage overhead.

  3. How do transaction isolation levels change application behavior?

    Isolation levels trade concurrency for protection against dirty, nonrepeatable, phantom, or serialization anomalies.

  4. How would you safely migrate a very large table?

    Use backward-compatible stages, bounded batches or online mechanisms, progress checkpoints, validation, and a rollback or forward-fix plan.

SQL Production Scenarios

  1. A query became slow after data volume grew. What is your investigation path?

    Compare plans and statistics, inspect selectivity and skew, identify changed scans or joins, then test the smallest evidence-based fix.

  2. Two workers occasionally process the same database job. How would you prevent it?

    Use an atomic claim with appropriate locking or compare-and-set semantics, a lease, and idempotent processing.

Additional Frequently Tested Questions

  1. How would you find why a SQL query became slow after data growth?

    Compare execution plans and runtime statistics, checking row estimates, scan types, joins, sorting, spills, locks, and the changed data distribution.

  2. How do transaction isolation levels affect concurrent analysis and updates?

    They define which intermediate or changed data a transaction may observe and which anomalies the database prevents.

  3. How would you remove duplicate rows while keeping the newest record?

    Rank rows within the business key by a deterministic freshness order, inspect the candidates, and delete or select only the preferred row in a controlled transaction.

  4. When can a common table expression improve or hurt a query?

    A CTE can clarify staged logic or recursion, but materialization and optimization behavior depend on the database and query, so the plan must be inspected.

  5. How do you calculate a retention cohort correctly in SQL?

    Define the cohort event and grain, assign each entity once, calculate activity periods consistently, and divide retained entities by the eligible cohort population.

  6. What makes keyset pagination more stable than offset pagination?

    Keyset pagination continues after a deterministic indexed sort key instead of repeatedly skipping an increasing number of rows.