SQL Coding Interview Questions for Testers (2026)

TL;DR

SQL coding interviews for testers evaluate whether you can turn a requirement into a trustworthy data oracle. Practice filters and nulls, joins, aggregation, subqueries, window functions, reconciliation, transactions, and query diagnosis, then prove each answer with edge cases and exact outputs.

Key Takeaways

• State the business grain, eligible population, key, null rule, tie rule, and expected output before writing a query.
• Practice on tiny fixtures whose exact row identifiers and values you can predict without a database.
• Use outer joins or `NOT EXISTS` for completeness, and inspect cardinality before calculating aggregates.
• Choose window partitions and ordering deliberately, with deterministic tie-breakers for latest-row problems.
• Reconciliation requires aligned populations and grains, key comparison in both directions, and meaningful measure checks.
• Treat parameter binding, least privilege, bounded queries, and ownership-based cleanup as part of a production-quality coding answer.
• Explain correctness first, then discuss dialect, complexity, indexes, plans, and operational tradeoffs.

SQL coding interview questions testers receive are rarely just syntax puzzles. Interviewers want to see whether you can translate a business rule into a query that catches missing, duplicate, stale, unauthorized, or inconsistent data without introducing false confidence. The strongest solution defines the population and grain before choosing a join or window function.

This guide is a runnable practice set for QA engineers, SDETs, backend testers, and data quality engineers. The examples use PostgreSQL-compatible SQL and mostly standard relational concepts. When a feature is dialect-specific, say so in an interview and adapt to the database named in the role.

TL;DR

Use this five-step loop: clarify -> model a tiny fixture -> write the simplest correct SQL -> predict exact output -> discuss edge cases and plan evidence.

1. SQL coding interview questions testers: A Better Answer Method

Before typing, restate the rule in relational terms. For example, "find paid orders without captured payments" contains at least four decisions: what states qualify as paid, whether multiple captures can satisfy one order, whether tenant and currency are part of the relationship, and whether recent records have an allowed processing delay. Ask only the questions that can change the query.

Declare the result grain. One row might represent an order, a duplicate email group, a customer, or a day. Then define the eligible population and complete business key. State null behavior, time boundaries, ties, and whether duplicates should be collapsed or reported. This makes the SQL reviewable.

Write a clear solution before optimizing. Use descriptive aliases and return diagnostic columns, such as key, expected value, actual value, and difference. Predict the result for an empty set, one valid row, a failing row, a duplicate, a null, another tenant, and a boundary timestamp.

Finally, discuss performance with evidence. Mention likely access paths or data volume only after correctness. Index usefulness, partition pruning, join strategy, and materialization vary by engine and statistics. The honest answer is to inspect the target database's plan and measure representative data, not claim that a CTE, subquery, or EXISTS is always faster.

2. Create a Runnable Fixture Before Solving Problems

A tiny fixture exposes mistakes faster than a large realistic dataset. The following PostgreSQL script creates customers, orders, and payments with intentional duplicates, missing relationships, partial payments, tenant separation, and tied timestamps. Run it in a disposable database. Temporary tables disappear when the session ends.

CREATE TEMP TABLE customers (
  tenant_id  integer NOT NULL,
  customer_id integer NOT NULL,
  email       text,
  status      text NOT NULL,
  created_at  timestamp NOT NULL,
  PRIMARY KEY (tenant_id, customer_id)
);

CREATE TEMP TABLE orders (
  tenant_id   integer NOT NULL,
  order_id    integer NOT NULL,
  customer_id integer NOT NULL,
  status      text NOT NULL,
  total_amount numeric(12, 2) NOT NULL,
  updated_at  timestamp NOT NULL,
  PRIMARY KEY (tenant_id, order_id)
);

CREATE TEMP TABLE payments (
  tenant_id   integer NOT NULL,
  payment_id  integer NOT NULL,
  order_id    integer NOT NULL,
  status      text NOT NULL,
  amount      numeric(12, 2) NOT NULL,
  created_at  timestamp NOT NULL,
  PRIMARY KEY (tenant_id, payment_id)
);

INSERT INTO customers VALUES
  (1, 1, 'alex@example.test', 'ACTIVE', '2026-07-01 09:00:00'),
  (1, 2, 'alex@example.test', 'ACTIVE', '2026-07-02 09:00:00'),
  (1, 3, NULL,                'ACTIVE', '2026-07-03 09:00:00'),
  (2, 1, 'alex@example.test', 'ACTIVE', '2026-07-01 09:00:00');

INSERT INTO orders VALUES
  (1, 101, 1, 'PAID',      50.00, '2026-07-10 10:00:00'),
  (1, 102, 2, 'PAID',      30.00, '2026-07-10 11:00:00'),
  (1, 103, 3, 'CANCELLED', 20.00, '2026-07-10 12:00:00'),
  (2, 101, 1, 'PAID',      70.00, '2026-07-10 10:00:00');

INSERT INTO payments VALUES
  (1, 1001, 101, 'CAPTURED', 30.00, '2026-07-10 10:01:00'),
  (1, 1002, 101, 'CAPTURED', 20.00, '2026-07-10 10:02:00'),
  (1, 1003, 102, 'FAILED',   30.00, '2026-07-10 11:01:00'),
  (2, 2001, 101, 'CAPTURED', 70.00, '2026-07-10 10:01:00');

The tenant column is deliberately repeated. Joining on order_id alone would connect tenant 1's order 101 to tenant 2's payment 2001 and create a convincing but false total. Good interview fixtures contain exactly this kind of adversarial record.

3. Filtering, NULL, CASE, and Time-Boundary Questions

A common prompt asks for active customers who have no email. The correct predicate is email IS NULL, not email = NULL. SQL comparisons with null normally produce unknown, and WHERE keeps only true. Do not replace null with an empty string unless the domain explicitly treats them as equivalent.

SELECT tenant_id, customer_id
FROM customers
WHERE status = 'ACTIVE'
  AND email IS NULL
ORDER BY tenant_id, customer_id;
-- Expected: (1, 3)

CASE is useful for classification, but overlapping conditions need intentional order. For payment reconciliation, classify exact, underpaid, and overpaid after aggregating captured payments. Do not compare each payment row to the whole order.

Time prompts frequently hide off-by-one errors. Prefer half-open ranges for timestamp intervals: created_at >= start_time AND created_at < end_time. A July 13 daily range then includes every supported precision after midnight and excludes exactly July 14, without inventing 23:59:59. Clarify whether boundaries are UTC, a business zone, or stored local time.

SELECT order_id
FROM orders
WHERE tenant_id = 1
  AND updated_at >= TIMESTAMP '2026-07-10 00:00:00'
  AND updated_at <  TIMESTAMP '2026-07-11 00:00:00'
ORDER BY order_id;

Also know the difference between COUNT(*), which counts rows, and COUNT(email), which counts non-null emails. COALESCE is a business decision, not a general null fix. Converting unknown payment to zero can be appropriate after an outer join, but converting unknown customer age to zero would change its meaning.

4. Join and Anti-Join Coding Problems

To find paid orders with no captured payment, preserve the complete order population and ask whether a matching captured row exists. NOT EXISTS expresses that intent directly. Correlate on the full relationship key, including tenant.

SELECT o.tenant_id, o.order_id, o.total_amount
FROM orders AS o
WHERE o.status = 'PAID'
  AND NOT EXISTS (
    SELECT 1
    FROM payments AS p
    WHERE p.tenant_id = o.tenant_id
      AND p.order_id = o.order_id
      AND p.status = 'CAPTURED'
  )
ORDER BY o.tenant_id, o.order_id;
-- Expected: (1, 102, 30.00)

A left anti-join is also valid: put eligible match conditions in ON, then filter p.payment_id IS NULL. Moving p.status = 'CAPTURED' to WHERE would remove the null-extended rows and undo the outer join. Check a non-nullable right-side key, not a nullable business attribute.

For completeness, put expected records on the preserved side. To find orphan payments, reverse the direction. A full outer join can classify source-only, target-only, and matched keys during reconciliation, although some engines require an equivalent union pattern.

Avoid nullable NOT IN unless you have proved the subquery cannot return null. One null in the candidate set can make every comparison unknown. NOT EXISTS is generally clearer for missing relationships. Learn the semantics rather than repeating a claim that one form is universally faster. The HTTP and API data validation guide provides related practice for comparing contract and persistence evidence.

5. Aggregation, Duplicates, and Cardinality Traps

To find duplicate active email addresses, decide whether uniqueness is global or tenant-scoped and whether comparison is case-sensitive or normalized by the business. The fixture expects uniqueness within a tenant. Exclude null explicitly if multiple unknown emails are allowed.

SELECT tenant_id, email, COUNT(*) AS duplicate_count
FROM customers
WHERE status = 'ACTIVE'
  AND email IS NOT NULL
GROUP BY tenant_id, email
HAVING COUNT(*) > 1
ORDER BY tenant_id, email;
-- Expected: tenant 1, alex@example.test, count 2

WHERE filters rows before grouping. HAVING filters groups after aggregates exist. A strong answer places lifecycle eligibility in WHERE and the duplicate threshold in HAVING, then explains why.

Aggregate reconciliation requires pre-aggregation. Order 101 has two captured payment rows, so join them and sum at order grain. If you also joined order items, each payment might repeat for each item. Aggregate payments to one row per tenant and order before joining another many-side dataset.

WITH captured AS (
  SELECT tenant_id, order_id, SUM(amount) AS captured_amount
  FROM payments
  WHERE status = 'CAPTURED'
  GROUP BY tenant_id, order_id
)
SELECT o.tenant_id, o.order_id, o.total_amount,
       COALESCE(c.captured_amount, 0) AS captured_amount
FROM orders AS o
LEFT JOIN captured AS c
  ON c.tenant_id = o.tenant_id
 AND c.order_id = o.order_id
WHERE o.status = 'PAID'
  AND o.total_amount <> COALESCE(c.captured_amount, 0)
ORDER BY o.tenant_id, o.order_id;
-- Expected mismatch: tenant 1, order 102, 30.00 versus 0

DISTINCT is not a safe repair for a wrong join. It may hide duplicated display rows while leaving sums wrong or removing legitimate identical facts. Fix the relationship and grain.

6. Subqueries, EXISTS, and Group-Relative Comparisons

A prompt such as "find orders above their tenant's average" requires comparing an order-grain value with a tenant-grain aggregate. You can use a grouped CTE, a correlated subquery, or a window function. State whether canceled orders participate in the average.

WITH tenant_average AS (
  SELECT tenant_id, AVG(total_amount) AS average_amount
  FROM orders
  WHERE status = 'PAID'
  GROUP BY tenant_id
)
SELECT o.tenant_id, o.order_id, o.total_amount
FROM orders AS o
JOIN tenant_average AS a
  ON a.tenant_id = o.tenant_id
WHERE o.status = 'PAID'
  AND o.total_amount > a.average_amount
ORDER BY o.tenant_id, o.order_id;
-- Expected: tenant 1, order 101

EXISTS answers whether at least one correlated row satisfies a condition. It does not conceptually need the selected value, so SELECT 1 communicates intent. IN tests membership in a one-column result. Both can be correct for positive membership, and optimizers can transform them. Focus on null semantics, full correlation, and readability.

A scalar subquery must return at most one row. If a prompt asks for the customer's last payment but ties are possible, a scalar subquery ordered only by timestamp is not a complete solution. Define the tie rule or use a window function with a stable secondary key.

For more targeted patterns, review SQL subqueries for testers. In an interview, do not force every problem into a CTE. Use the form that makes grain and eligibility obvious, then discuss how you would inspect the real plan.

7. Window Function Coding Questions

Window functions calculate across related rows without collapsing them. The critical choices are PARTITION BY, ORDER BY, and, for aggregate windows, the frame. To retrieve the latest payment attempt per order, partition by the complete order identity and order by authoritative chronology plus a deterministic tie-breaker.

WITH ranked AS (
  SELECT p.*,
         ROW_NUMBER() OVER (
           PARTITION BY tenant_id, order_id
           ORDER BY created_at DESC, payment_id DESC
         ) AS rn
  FROM payments AS p
)
SELECT tenant_id, order_id, payment_id, status, amount
FROM ranked
WHERE rn = 1
ORDER BY tenant_id, order_id;

ROW_NUMBER assigns a distinct sequence. RANK gives ties the same rank and leaves gaps, while DENSE_RANK leaves no gaps. For "second highest distinct salary", DENSE_RANK = 2 often expresses the requested semantics, but clarify whether all tied employees should be returned.

LAG and LEAD compare adjacent versions. They are useful for detecting status transitions, price changes, or overlapping effective intervals. Ordering must reflect business sequence, not merely insertion order.

Running totals need an explicit frame when duplicate sort keys exist. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW counts physical ordered rows; a default peer-aware frame may include tied rows together depending on the engine and expression. Add a deterministic ordering column and say which behavior the requirement needs.

8. Reconciliation and ETL Coding Scenarios

A trustworthy reconciliation aligns source and target to the same grain and eligibility. Start with key coverage in both directions. Then compare fields and measures after normalizing only documented representation differences. If the source has order-line grain and the target has order grain, aggregate the source before joining.

Use a full outer join where supported to label SOURCE_ONLY, TARGET_ONLY, MATCH, and VALUE_MISMATCH. Keep null-safe equality in mind. PostgreSQL provides IS DISTINCT FROM, which treats two nulls as equal for comparison purposes and one null as different. Other engines have different operators or require expanded logic.

-- PostgreSQL example for two order-grain tables
SELECT COALESCE(s.tenant_id, t.tenant_id) AS tenant_id,
       COALESCE(s.order_id, t.order_id) AS order_id,
       CASE
         WHEN s.order_id IS NULL THEN 'TARGET_ONLY'
         WHEN t.order_id IS NULL THEN 'SOURCE_ONLY'
         WHEN s.total_amount IS DISTINCT FROM t.total_amount THEN 'VALUE_MISMATCH'
         ELSE 'MATCH'
       END AS result
FROM source_orders AS s
FULL OUTER JOIN target_orders AS t
  ON t.tenant_id = s.tenant_id
 AND t.order_id = s.order_id;

Hashes can accelerate comparison but require a canonical contract for column order, type formatting, null markers, whitespace, case, time zone, and encoding. Never concatenate values without unambiguous separators or length encoding. A matching hash supports equality under that canonicalization. A mismatch must lead to field-level evidence.

Also model expected latency. A just-created source record may legitimately be absent until the pipeline's freshness objective expires. Freeze the comparison population with a cutoff or batch identity rather than querying two moving systems at different moments.

9. Safe Data Setup, Mutation, and Transaction Questions

Testers may be asked to create fixtures or clean them up. Use a transaction in a disposable database when the engine and application behavior allow rollback. In shared environments, namespace rows with a run ID and delete only those owned rows. Never write a cleanup such as DELETE FROM orders because a filter variable happened to be empty.

Parameterized statements separate values from SQL structure. The exact placeholder differs by driver, but the principle is stable. Do not concatenate an email, tenant, or timestamp into SQL. Object identifiers usually cannot use ordinary value binds, so map them from a trusted allowlist rather than accepting arbitrary input.

Transactions deserve concurrency tests. For a unique business key, coordinate two connections attempting the same insert. Expect at most one committed logical record and a documented safe error or retry path for the other. Verify the final table, API response mapping, events, and audit record. A unit test around one connection cannot prove the race.

Database setup should support the test, not couple every assertion to internal storage. Use direct SQL when validating persistence or preparing otherwise difficult state. Prefer public APIs when the user contract is the subject. The test data setup and teardown guide covers ownership and cleanup patterns in more detail.

10. Performance and Query-Plan Interview Answers

If a correct query is slow, capture the actual query, parameters, plan, data distribution, statistics state, concurrency, and timing breakdown in an approved environment. Check whether filters are selective, join keys are complete, estimates are reasonable, sorts or hashes spill, partitions prune, and the result transfer dominates.

An index is not automatically the answer. It can speed selective reads while adding write cost and storage, and it may not help a query returning much of a table. Composite index order and supported index types depend on access patterns and engine. State the query you want to improve, then verify with the engine's explain facility and representative data.

Avoid wrapping an indexed column in a function when an equivalent range predicate is clearer and more searchable. For example, use a half-open timestamp range rather than comparing DATE(created_at) when appropriate. Still verify the target engine because expression indexes and optimizer behavior vary.

For automation, bound diagnostic queries with tenant, run ID, batch, time, and sensible limits. A failing assertion should report sample keys and total mismatch count, not fetch millions of rows into CI memory. Keep secrets and sensitive data out of plans and logs.

11. SQL coding interview questions testers: Practice Plan

Create a daily set of four problems: one filter or null problem, one join or aggregation, one window function, and one QA reconciliation scenario. Solve each first on paper with a five-row fixture. Then run it in the target database and compare exact output. Correct any difference in your mental model.

Rotate domains so syntax does not become tied to one story: orders and payments, subscriptions and invoices, users and roles, devices and readings, test runs and failures, tickets and status history. Reuse the same relational patterns while changing keys, grain, time, and edge cases.

During mock interviews, narrate decisions without reading every keystroke. Say, "I preserve all paid orders on the left, aggregate captured payments to order grain, then compare totals. I include tenant in the key and expect order 102 as the only mismatch." That is concise, testable reasoning.

Use the companion general SQL interview guide for testers for concepts, then return here for timed coding. Keep a mistake journal for nulls, joins, grouping, time, ties, mutation, and dialect differences. Re-solving your own errors produces stronger recall than collecting more questions.

Interview Questions and Answers

Q: Write SQL to find duplicate active emails per tenant.

Filter active rows, exclude null if the domain permits multiple unknown emails, group by tenant and normalized email, then use HAVING COUNT(*) > 1. Return the key and count. Clarify case normalization and whether inactive accounts reserve an email.

Q: Find paid orders without a captured payment.

Use NOT EXISTS correlated by the complete tenant and order key, with captured status inside the subquery. This preserves all eligible paid orders and asks whether a qualifying payment is absent. Test a failed-only payment, a payment in another tenant, and a recent order within any allowed lag.

Q: Why can a left join accidentally behave like an inner join?

A right-table predicate in WHERE rejects null-extended rows because the comparison is not true. Put match eligibility in ON when missing right rows must remain. Then test a non-nullable right key for absence.

Q: Return the latest row for every customer.

Use ROW_NUMBER() partitioned by the full customer key and ordered by authoritative timestamp descending plus a stable unique tie-breaker. Filter row number 1 in an outer query. Clarify late arrivals, ties, and whether latest means event time or ingestion time.

Q: Find the second highest distinct value.

Use DENSE_RANK() ordered by the value descending and return rank 2 when all rows with the second distinct value should appear. If the prompt wants one row, define the tie rule. Test fewer than two distinct values and null participation.

Q: What is wrong with NOT IN when the subquery returns NULL?

The null makes candidate comparisons unknown, so the predicate may return no rows. Use a null-safe formulation, often NOT EXISTS with a complete correlation. Also test null in the outer expression and confirm the intended business treatment.

Q: How do you prevent inflated sums after a join?

Declare cardinality and target grain. Aggregate each many-side dataset to that grain before joining, or calculate the measure before introducing another many-side relationship. DISTINCT is not a general correction for duplicated facts.

Q: How would you compare source and target tables?

Freeze equivalent populations, align grain and types, compare keys in both directions, then compare material fields and measures. Classify source-only, target-only, and value mismatches with diagnostic keys. State expected latency, null, rounding, and duplicate rules.

Q: What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after aggregates are calculated. For duplicate active emails, active status belongs in WHERE and COUNT(*) > 1 belongs in HAVING.

Q: How do you test a SQL solution?

I use a tiny fixture with empty, ordinary, boundary, duplicate, null, missing relationship, another tenant, and tie cases. I predict exact identifiers and values, execute the query, and compare. I also inspect whether input mutation, time, or concurrency changes the result.

Q: How would you make a query safe in automation?

Use least-privileged credentials, supported parameter binding for values, trusted mapping for identifiers, and bounds by tenant, run, batch, or time. Return diagnostic samples plus counts, close resources, and avoid sensitive payloads in logs. Cleanup touches only data owned by the run.

Q: How do you optimize a slow SQL query?

Prove correctness first, then inspect the target database's actual or safe explain plan with representative parameters and data. Look at row estimates, scans, join shape, sorts, spills, and transfer. Change one factor, remeasure, and verify the result remains correct.

Common Mistakes

• Writing SQL before clarifying grain, business key, eligible states, nulls, time, and ties.
• Using = NULL or treating null, zero, empty string, and missing relationship as identical.
• Omitting tenant or another scope dimension from a join.
• Moving a right-side eligibility predicate from ON to WHERE in an outer join.
• Using DISTINCT to hide a cardinality error or join multiplication.
• Choosing the latest row by timestamp without a deterministic tie-breaker.
• Comparing row counts without checking keys, duplicates, values, and freshness.
• Concatenating user-controlled values or object names into automation SQL.
• Claiming that one query form or index is always faster without plan evidence.
• Running destructive setup or unbounded diagnostics in a shared environment.

Conclusion

SQL coding interview questions testers face are data-oracle problems disguised as syntax exercises. Make your assumptions visible, preserve the correct population, control grain, handle nulls and ties, and prove exact output on an adversarial fixture. Then discuss safety and performance with database-specific evidence.

Run the fixture in this guide, rewrite each answer without looking, and add one edge case that breaks the obvious solution. That habit builds the reasoning QA and SDET interviewers can trust in production.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Java Coding Interview Questions for Testers (2026)
• JavaScript Coding Interview Questions for Testers (2026)
• Python Coding Interview Questions for Testers (2026)
• SDET Coding Interview Questions for Testers (2026)
• SQL Interview Questions for QA and Testers (2026)
• Agile Scrum Interview Questions for Testers