SQL & PostgreSQL Interview Questions: Queries, Indexing, and Optimization

SQL interviews differ from most other technical interviews in one important way: they're frequently live and hands-on, with an interviewer watching you write and iterate on a query in real time, sometimes against an actual (or simulated) schema. This makes fluency — not just conceptual knowledge — the thing actually being tested. This guide covers the concepts and query patterns that come up most often, with the reasoning behind each.

Why SQL Interviews Blend Theory and Live Problem-Solving

Unlike a behavioral question, there's rarely ambiguity about whether a SQL answer is correct — the query either returns the right rows or it doesn't. That objectivity is exactly why SQL rounds are so commonly hands-on: it's a fast, unambiguous way to assess real competence, and it also reveals how you debug when a query returns something unexpected, which is a skill textbook knowledge alone doesn't demonstrate.

Query Fundamentals Interviewers Expect

"Explain the difference between INNER JOIN, LEFT JOIN, and a self-join."

• INNER JOIN returns only rows with a match in both tables.
• LEFT JOIN returns all rows from the left table, with NULL for unmatched columns from the right table — commonly used when you want every row from the primary table regardless of whether a related record exists.
• Self-join joins a table to itself, typically used for hierarchical data (an employees table with a manager_id referencing another row in the same table).

"Why might a JOIN return more rows than you expected?" This is one of the most common practical stumbling blocks: if the joined table has multiple matching rows for a single row in the other table (a one-to-many relationship), the result set duplicates the "one" side once per match. A candidate who can immediately diagnose this — rather than being confused by unexpected duplicate rows — signals real hands-on experience.

Worked Query Examples

Prompt: "Find the top 3 highest-paid employees in each department."

SELECT department, name, salary
FROM (
  SELECT
    department,
    name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

This is a strong opportunity to explain window functions explicitly: PARTITION BY resets the ranking calculation within each department, and RANK() handles ties by assigning the same rank to equal values (as opposed to ROW_NUMBER(), which would break ties arbitrarily).

Prompt: "Find customers who placed an order in the last 30 days but haven't placed one in the 30 days before that."

SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '60 days'
      AND order_date < CURRENT_DATE - INTERVAL '30 days'
  );

Talking through your reasoning out loud here — "I need recent activity, minus anyone active in the prior window" — matters as much as the final query, since interviewers are evaluating how you decompose the problem, not just the syntax.

Indexing and Performance Questions

"What does an index actually do, and what's the trade-off?" An index is a separate data structure (commonly a B-tree) that lets the database locate rows matching a condition without scanning the entire table. The trade-off: indexes speed up reads that use them, but slow down writes (every INSERT/UPDATE/DELETE has to update the index too) and consume additional storage — so indexing every column "just in case" is a common anti-pattern, not a best practice.

"When would adding an index not help?"

• If the query filters on a column with very low selectivity (e.g., a boolean with a near-even split), the database may reasonably choose a full table scan over using the index anyway.
• If the query uses a function or expression on the indexed column (WHERE UPPER(name) = 'X') without a matching expression index, the plain index on name won't be used.
• On a small table, a sequential scan is often faster than the overhead of using an index at all.

"How would you diagnose a slow query?" Start with EXPLAIN ANALYZE to see the actual query plan and execution time per step, looking specifically for sequential scans on large tables where an index scan would be expected, and for any step whose estimated row count diverges wildly from the actual row count — a strong signal that table statistics are stale.

A candidate who reaches for EXPLAIN ANALYZE before guessing at a fix is demonstrating exactly the instinct interviewers are trying to test for.

Data Modeling Questions

"What is normalization, and when would you deliberately denormalize?" Normalization organizes data to minimize redundancy — typically up to third normal form in practice, where each non-key column depends only on the table's primary key. Denormalization deliberately reintroduces some redundancy (duplicating a value across tables, or pre-computing an aggregate) to avoid expensive joins on read-heavy paths, at the cost of more complex writes to keep the duplicated data consistent. A good answer frames this explicitly as a read/write trade-off, not a rule being broken.

PostgreSQL-Specific Questions

• Transactions and isolation levels: PostgreSQL defaults to Read Committed isolation. Be ready to explain what a "dirty read," "non-repeatable read," and "phantom read" are, and roughly where each isolation level (Read Committed, Repeatable Read, Serializable) draws the line.
• JSONB: PostgreSQL's binary JSON type supports indexing and efficient querying of semi-structured data — useful when a schema needs some flexibility without going fully schemaless, though it shouldn't replace proper relational modeling for structured, frequently-queried fields.
• Common functions: window functions (RANK, LAG, LEAD), COALESCE for default values, and ON CONFLICT for upsert behavior are all frequent practical topics.

Common Mistakes During Live SQL Rounds

• Forgetting GROUP BY needs to include every non-aggregated column in the SELECT clause
• Using NOT IN with a subquery that could return NULL, which silently causes the entire condition to evaluate as unknown (and effectively no rows match) — NOT EXISTS is usually the safer choice
• Not testing edge cases out loud (empty result sets, NULL values in join columns) before declaring the query finished
• Jumping straight to a complex query instead of describing the plain-English logic first, which makes it harder for the interviewer to follow your reasoning if something goes wrong

How to Prepare

Practice writing SQL against an unfamiliar schema under time pressure, out loud, rather than only reviewing syntax silently. The skill being tested isn't just "do you know SQL" — it's whether you can reason through an unfamiliar data model, catch your own mistakes, and explain your thinking clearly while someone is watching, which is a meaningfully different skill than solving the same problem alone with no time limit.