These 27 SQL interview questions are for hiring developers who write and tune SQL against production databases, whether they are backend engineers, analytics engineers or dedicated database developers. Examples use PostgreSQL, with notes where SQL Server and MySQL behave differently. At Ryz, SQL candidates are sourced by recruiters who look at the systems they worked on, then complete structured NTRVSTA AI interviews built around problems like these, with recruiters reviewing each candidate before and after. AI scores are advisory, and people make the decisions.
SQL interviews often stop at "write a join". That filters out almost no one. The useful signal comes from NULL handling, plan reading and concurrency, because those are where production bugs and slow pages come from.
SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';
For customers with no orders, o.status is NULL, so the WHERE filter removes them and the left join behaves like an inner join. Move the condition into the join: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'.
What a strong answer shows: They know where filters apply in outer joins, the most common silent bug in reporting SQL.
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
If any orders.customer_id is NULL, id NOT IN (...) evaluates to unknown for every row, never true. Use NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id), which handles NULLs correctly and usually plans as an anti-join.
What a strong answer shows: Three-valued logic and a habit of using NOT EXISTS.
WHERE and HAVING?WHERE filters rows before grouping; HAVING filters groups after aggregation. Conditions that do not depend on aggregates belong in WHERE, so fewer rows reach the aggregation step.
What a strong answer shows: They understand logical query processing order, not just syntax.
UNION versus UNION ALL?UNION removes duplicates, which costs a sort or hash over the combined result. Use UNION ALL when the inputs cannot overlap or duplicates are meaningful, which is most of the time.
What a strong answer shows: Awareness that a small keyword choice has a real cost.
COUNT(*), COUNT(col) and COUNT(DISTINCT col) return?COUNT(*) counts rows. COUNT(col) counts non-NULL values in that column. COUNT(DISTINCT col) counts distinct non-NULL values. Mixing them up produces totals that look right until NULLs appear. Note that SUM over zero rows returns NULL, not 0, so reports need COALESCE.
What a strong answer shows: Careful NULL handling in aggregates.
Bigint identity columns are compact and keep inserts at the end of the index. Random UUIDv4 keys scatter inserts across the B-tree, causing page splits and poor cache use, but they can be generated anywhere and do not leak counts. Time-ordered UUIDv7 keeps most of the benefits of both; PostgreSQL 18 added a built-in uuidv7() function. In SQL Server, clustering on random GUIDs is especially costly because the table is stored in key order.
What a strong answer shows: They tie key choice to storage layout and write patterns.
Start normalized so each fact lives in one place. Denormalize when a measured read path needs it: a cached total on an order, a counter column, a materialized view for a dashboard. Each copy needs a clear update path and a way to detect drift.
What a strong answer shows: Denormalization as a deliberate, maintained trade-off.
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY created_at DESC, id DESC) AS rn
FROM orders o
) ranked
WHERE rn = 1;
Adding id breaks ties so the result is deterministic. In Postgres, SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, created_at DESC, id DESC is shorter. With an index on (customer_id, created_at DESC) and few customers, a LATERAL join with LIMIT 1 per customer can be faster still.
What a strong answer shows: Deterministic ordering and more than one approach with a sense of when each wins.
With ORDER BY and no frame clause, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all peer rows with the same sort value. Rows sharing a timestamp get the same total.
SELECT id, created_at, amount,
SUM(amount) OVER (PARTITION BY wallet_id
ORDER BY created_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
FROM ledger_entries;
What a strong answer shows: They know window frames, not just window functions.
WITH RECURSIVE chain AS (
SELECT id, manager_id, name, 1 AS depth
FROM employees WHERE id = 42
UNION ALL
SELECT e.id, e.manager_id, e.name, c.depth + 1
FROM employees e
JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain ORDER BY depth;
SQL Server omits the RECURSIVE keyword; MySQL 8 supports it. Guard against cycles in bad data with a depth limit or, in Postgres 14 and later, the CYCLE clause.
What a strong answer shows: Recursive CTE structure plus defensive handling of cycles.
In Postgres before version 12, yes: every CTE was materialized. Since 12, a non-recursive CTE referenced once and without side effects is inlined, and you can force behavior with AS MATERIALIZED or AS NOT MATERIALIZED. SQL Server always inlines CTEs, so a CTE referenced twice runs twice; a temp table is the way to materialize.
What a strong answer shows: Version- and engine-specific knowledge instead of folklore.
OFFSET 250000 still reads and discards 250,000 rows. Use keyset pagination: remember the last row's sort key and continue from it, backed by an index on (created_at, id).
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;
SQL Server does not support row-value comparison, so expand it: created_at < @c OR (created_at = @c AND id < @id).
What a strong answer shows: They know why offset degrades and how keyset needs a unique tiebreaker.
INSERT INTO inventory (sku, qty)
VALUES ($1, $2)
ON CONFLICT (sku) DO UPDATE
SET qty = inventory.qty + EXCLUDED.qty;
ON CONFLICT relies on a unique index and is safe against concurrent inserts. MERGE, available in Postgres 15 and SQL Server, is more flexible but can still fail with duplicate-key errors under concurrency; in SQL Server it is usually paired with a HOLDLOCK hint. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE.
What a strong answer shows: They know upserts depend on constraints and that MERGE is not automatically atomic.
SELECT d::date AS day, COALESCE(SUM(o.total), 0) AS revenue
FROM generate_series(date '2026-09-01', date '2026-09-30', interval '1 day') AS d
LEFT JOIN orders o
ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d
ORDER BY d;
Generate the full date range and left join the facts to it. SQL Server 2022 added GENERATE_SERIES; elsewhere a calendar table works. Use half-open ranges instead of casting the column, which keeps an index on created_at usable, and be explicit about the time zone that defines a day.
What a strong answer shows: Correct reporting logic that stays index-friendly.
Run EXPLAIN (ANALYZE, BUFFERS) in Postgres, or capture the actual plan in SQL Server. Read from the innermost nodes out. Compare estimated rows with actual rows: large mismatches point to stale statistics or correlated columns, and they cause bad join choices, such as a nested loop running a million times.
What a strong answer shows: They hunt for estimate errors instead of treating every sequential scan as a problem.
Columns used with equality first, then the range or sort column. For WHERE tenant_id = $1 AND status = 'open' AND created_at >= $2 ORDER BY created_at, an index on (tenant_id, status, created_at) serves both the filter and the sort. A column after a range condition cannot narrow the scan in the usual B-tree access path.
CREATE INDEX CONCURRENTLY orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at);
What a strong answer shows: Index design driven by actual query shapes.
email, but the login query still scans the table. Why?Typical causes: the query wraps the column in a function, as in WHERE lower(email) = ...; a type mismatch forces a cast on the column; a leading-wildcard LIKE '%@example.com'; or statistics that make the planner think a scan is cheaper. Fix the predicate, or add an expression index that matches it, such as CREATE INDEX ON users (lower(email)), or use a case-insensitive collation or citext.
What a strong answer shows: They know the index must match the predicate exactly as written.
A covering index stores extra columns with INCLUDE so the query can be answered from the index alone; in Postgres an index-only scan also needs the visibility map to be current, which vacuum maintains. A partial index (filtered index in SQL Server) indexes only rows matching a condition, which keeps it small for hot subsets.
CREATE INDEX ON orders (customer_id, created_at) INCLUDE (total);
CREATE INDEX ON jobs (run_at) WHERE status = 'pending';
What a strong answer shows: Targeted indexes and knowledge of the vacuum dependency.
Likely a plan change. In SQL Server this is often parameter sniffing: a plan compiled for a rare parameter value is reused for common ones. Query Store shows plan history and lets you force a good plan; OPTION (RECOMPILE) or OPTIMIZE FOR are targeted fixes. In Postgres, prepared statements can switch to a generic plan after several executions, which plan_cache_mode controls, and stale statistics after a bulk load are a common trigger; run ANALYZE. Also check whether the deploy changed the query text or parameter types.
What a strong answer shows: They know plans are cached and can change without schema changes, and know engine-specific tools.
Every index slows inserts and updates, takes memory and disk, and in Postgres can block HOT updates, which increases bloat. Find unused indexes with pg_stat_user_indexes or sys.dm_db_index_usage_stats, drop duplicates covered by a wider index, and add indexes for proven query patterns.
What a strong answer shows: They weigh read gains against write and storage costs.
Postgres and SQL Server default to Read Committed; SQL Server uses locking reads unless READ_COMMITTED_SNAPSHOT is on, which is the default in Azure SQL Database. MySQL InnoDB defaults to Repeatable Read with next-key locks. Postgres Repeatable Read is snapshot isolation, and Serializable adds conflict detection that aborts transactions with error 40001, so applications must retry. Write skew is the classic anomaly snapshot isolation allows.
What a strong answer shows: Engine-specific defaults and the retry obligation that comes with stronger isolation.
Get the deadlock details: Postgres logs both statements, and SQL Server records a deadlock graph in the system health session. The usual cause is two transactions locking the same rows in different orders. Fix it by locking in a consistent order, for example sorting IDs before updating, keeping transactions short, adding indexes so updates do not lock more rows than needed, and retrying the victim transaction.
What a strong answer shows: Lock ordering as the root fix, with retries as a safety net.
Since Postgres 11, adding a column with a constant default is a metadata change. For a backfilled value, add the column as nullable, backfill in small batches, then add CHECK (col IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT, which does not block writes, and finally SET NOT NULL, which uses the validated check to skip a full scan. Set a short lock_timeout so DDL fails fast instead of queuing behind long transactions.
What a strong answer shows: Lock levels and a step-by-step migration plan.
When a table is large and queries or maintenance align with a key, usually time. Range partitioning by month lets queries prune old partitions and lets you drop old data by detaching a partition instead of deleting rows. It adds cost: every unique constraint must include the partition key, and queries that do not filter on it touch every partition.
What a strong answer shows: Partitioning for data lifecycle and pruning, not as a generic speed-up.
Long-running or idle-in-transaction sessions hold back the xmin horizon, so vacuum cannot remove dead rows; check pg_stat_activity, replication slots and long-lived replica queries with hot_standby_feedback. Tune autovacuum per table for high-churn tables, and set idle_in_transaction_session_timeout. Watch transaction ID age to stay well clear of wraparound.
What a strong answer shows: MVCC internals and operational causes, not just "run VACUUM FULL".
Separate the workloads. Short term: a read replica, or materialized views refreshed on a schedule; REFRESH MATERIALIZED VIEW CONCURRENTLY requires a unique index on the view. Longer term: replicate changes into a warehouse or columnar store built for scans and aggregations. Set statement timeouts for ad hoc users either way.
What a strong answer shows: Protecting transactional workloads with a path that scales.
DELETE FROM contacts c
USING (
SELECT id, ROW_NUMBER() OVER (PARTITION BY lower(email)
ORDER BY created_at, id) AS rn
FROM contacts
) d
WHERE c.id = d.id AND d.rn > 1;
First repoint child rows that reference the duplicates, run it in batches on large tables, and add a unique index on lower(email) afterward so it cannot happen again.
What a strong answer shows: They handle dependent data and prevent recurrence, not just the delete.
NOT IN with subqueries and cannot explain NULL behavior.OFFSET values and does not see a problem.Provide a Postgres database in Docker with a realistic schema, such as customers, orders, order lines and payments, loaded with a few million rows. Give the candidate three tasks for a 90-minute session: write a monthly cohort retention query with window functions, speed up two slow queries using their execution plans, and plan a migration that adds a required column to the largest table. Candidates who prefer SQL Server can get an equivalent setup.
EXPLAIN (ANALYZE, BUFFERS) output and explain the estimate errors?Ryz introduces senior SQL developers who have been assessed on query correctness, plan reading, indexing and safe changes to production databases. They are the top 1% of the candidates we interview, they work on your team and repos, and they keep hours within ±1h of US time zones. See how our vetting process works, or start from our SQL developer job description.
Test concepts in the candidate's strongest engine, then ask how they would verify behavior in yours. Joins, NULLs and window functions transfer directly. Defaults, locking behavior and tooling differ, so a senior hire should know where to check.
It checks query writing but misses plans, indexing and concurrency. Use one puzzle at most, then switch to a real schema with enough data that performance matters.
Share a hosted or containerized database and let the candidate use their own client. Watching them explore an unfamiliar schema tells you more than a whiteboard query.
Questions we didn't answer? Email info@ryzlabs.com.