SQL Interview Questions and Answers

Answer

SQL is written in one order but executed in another, and this single fact explains most beginner errors. The logical execution order is: FROM (including JOINs), then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT/OFFSET. Because SELECT runs after WHERE, you cannot use a column alias defined in SELECT inside the WHERE clause: WHERE total_amount > 100 fails with 'Unknown column' in MySQL if total_amount is an alias, because at WHERE time the alias does not exist yet.

ORDER BY runs after SELECT, which is why aliases DO work there. Similarly, WHERE cannot contain aggregate functions because aggregation has not happened yet; that is exactly what HAVING is for. Interviewers use this to separate people who have memorised syntax from people who understand the model.

A classic probe: 'why does WHERE COUNT(*) > 5 fail?' The answer is that COUNT is computed during GROUP BY, which happens after WHERE. Another: MySQL (with ONLY_FULL_GROUP_BY disabled) lets you reference aliases in GROUP BY and HAVING as an extension, while PostgreSQL allows aliases in GROUP BY but standard SQL technically does not, so knowing what is portable versus dialect sugar earns extra credit. When you debug a wrong result, mentally replay the pipeline in logical order: which rows survived the join, which were filtered, how they were grouped, and only then what was projected.

-- Fails in MySQL: alias not visible in WHERE
SELECT amount * 1.18 AS with_gst
FROM orders
WHERE with_gst > 1000;  -- ERROR 1054: Unknown column 'with_gst'

-- Correct: repeat the expression, or use a derived table
SELECT amount * 1.18 AS with_gst
FROM orders
WHERE amount * 1.18 > 1000
ORDER BY with_gst DESC;  -- alias OK here: ORDER BY runs after SELECT

-- Aggregates filter with HAVING, never WHERE
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

Answer

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. WHERE cannot reference aggregate functions because it runs before GROUP BY, while HAVING exists precisely to filter on aggregates like COUNT(*), SUM(amount), or AVG(rating). The performance implication is what interviewers actually want to hear: conditions in WHERE reduce the number of rows that enter the grouping step, so a filter that can live in WHERE should never be moved to HAVING.

Writing HAVING city = 'Bengaluru' works in most engines (a non-aggregate condition on a grouped column is legal), but it forces the database to group all cities and discard the unwanted groups afterwards, doing strictly more work than WHERE city = 'Bengaluru'. Modern optimisers in PostgreSQL and MySQL 8 will often push such predicates down automatically, but relying on the optimiser to fix sloppy SQL is a bad habit and does not always work across engines. A second nuance worth naming: HAVING can reference aggregates that are not in the SELECT list, for example SELECT customer_id FROM orders GROUP BY customer_id HAVING SUM(amount) > 100000, which is a common pattern for 'high-value customers' style questions. Finally, in MySQL HAVING can reference SELECT aliases (HAVING order_count > 5), which is convenient but non-portable; PostgreSQL rejects it, so in a dialect-neutral interview repeat the aggregate expression instead.

-- Row filter (WHERE) + group filter (HAVING) together
SELECT customer_id,
       SUM(amount) AS lifetime_value
FROM orders
WHERE status = 'DELIVERED'          -- filters rows before grouping
  AND created_at >= '2025-01-01'
GROUP BY customer_id
HAVING SUM(amount) > 100000;        -- filters groups after aggregation

-- Anti-pattern: non-aggregate filter in HAVING
-- SELECT city, COUNT(*) FROM stores GROUP BY city HAVING city = 'Pune';
-- Works, but groups every city first. Put city = 'Pune' in WHERE.

Answer

INNER JOIN returns only rows where the join condition matches in both tables. LEFT JOIN returns every row from the left table, with NULLs filling the right-side columns when there is no match. RIGHT JOIN is the mirror image and is rarely used in practice because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order, which most style guides prefer for readability.

FULL OUTER JOIN returns all rows from both sides, matching where possible and NULL-padding where not; PostgreSQL supports it natively, but MySQL still does not (as of MySQL 8.4), so on MySQL you emulate it with a LEFT JOIN UNION ALL an anti-joined RIGHT JOIN. The production gotcha interviewers love: putting a filter on the right table in the WHERE clause of a LEFT JOIN silently converts it into an INNER JOIN. WHERE orders.status = 'DELIVERED' eliminates the NULL rows that made the join 'left' in the first place, so customers without orders vanish from the result.

The fix is to move that condition into the ON clause. Also be ready to explain that a join is conceptually a filtered Cartesian product, and that joining on a non-unique key multiplies rows: joining customers to orders on customer_id produces one row per order, not per customer, which is why COUNT(*) after a join often surprises people.

-- Customers and their orders, keeping customers with zero orders
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'DELIVERED';   -- filter belongs in ON, not WHERE

-- WRONG: this silently becomes an INNER JOIN
-- LEFT JOIN orders o ON o.customer_id = c.customer_id
-- WHERE o.status = 'DELIVERED';

-- FULL OUTER JOIN emulation on MySQL (no native support)
SELECT c.customer_id, o.order_id FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT c.customer_id, o.order_id FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Answer

NULL is not a value; it is a marker for 'unknown', and every comparison with it yields UNKNOWN rather than TRUE or FALSE. That gives SQL three-valued logic: TRUE, FALSE, UNKNOWN. A WHERE clause only keeps rows where the predicate is TRUE, so both WHERE discount = NULL and WHERE discount <> NULL return zero rows, always.

You must write IS NULL or IS NOT NULL. This leaks into surprising places. NOT IN with a NULL in the subquery result returns no rows at all, because x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and that last term is UNKNOWN, poisoning the whole conjunction.

Aggregate functions skip NULLs: COUNT(column) ignores NULL rows while COUNT(*) counts them, AVG divides by the count of non-NULL values only, and SUM of an empty or all-NULL set returns NULL rather than 0 (wrap it in COALESCE(SUM(x), 0) for reports). In GROUP BY and DISTINCT, NULLs are treated as equal to each other and form one group, which is inconsistent with comparison semantics but is what the standard mandates. Unique constraints differ by engine: PostgreSQL and MySQL both allow multiple NULLs in a UNIQUE column by default, though PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to change that. COALESCE(a, b, c) returns the first non-NULL argument, and NULLIF(a, b) returns NULL when a equals b, which is the standard trick to avoid division-by-zero: amount / NULLIF(quantity, 0).

-- Both return ZERO rows regardless of data
SELECT * FROM orders WHERE discount = NULL;
SELECT * FROM orders WHERE discount <> NULL;

-- Correct
SELECT * FROM orders WHERE discount IS NULL;

-- The NOT IN trap: returns no rows if any referrer_id is NULL
SELECT * FROM users
WHERE id NOT IN (SELECT referrer_id FROM signups);

-- Safe version
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM signups s WHERE s.referrer_id = u.id
);

-- Division-by-zero guard and NULL-safe totals
SELECT COALESCE(SUM(amount), 0) / NULLIF(COUNT(DISTINCT day), 0)
FROM orders;

Answer

For plain deduplication of a projection they produce identical results and usually identical query plans: SELECT DISTINCT city FROM stores and SELECT city FROM stores GROUP BY city both collapse to unique cities, and both PostgreSQL and MySQL will typically choose the same hash or sort strategy for either spelling. The difference is intent and capability. GROUP BY exists to compute aggregates per group; DISTINCT exists to remove duplicate rows from a result.

The moment you need COUNT, SUM, MIN, or MAX per group, only GROUP BY works. Using DISTINCT as a band-aid is a smell interviewers watch for: candidates who write a join that fans out rows (joining on a non-unique key) and then slap DISTINCT on top to 'fix' duplicates are hiding a modelling error, and the fix is usually a better join condition, a semi-join with EXISTS, or aggregating before joining. Two dialect-specific extensions are worth knowing.

PostgreSQL has DISTINCT ON (expr), which keeps the first row per group according to ORDER BY, a very concise way to get 'latest order per customer' without window functions. MySQL has no DISTINCT ON; you use window functions or a correlated subquery instead. Also note COUNT(DISTINCT col) is a legitimate, common aggregate, and that DISTINCT applies to the whole selected row, not just the column it is written next to, a misreading that produces frequent confusion in code review.

-- Equivalent for pure dedup
SELECT DISTINCT city FROM stores;
SELECT city FROM stores GROUP BY city;

-- Only GROUP BY can aggregate
SELECT city, COUNT(*) AS store_count
FROM stores
GROUP BY city;

-- PostgreSQL only: latest order per customer in one pass
SELECT DISTINCT ON (customer_id)
       customer_id, order_id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

Answer

A primary key uniquely identifies each row, allows no NULLs, and each table can have exactly one; it is almost always backed by a unique index automatically. A unique constraint also enforces uniqueness but a table can have many of them, and by default they allow NULLs (multiple NULLs in both MySQL and PostgreSQL, unless you use PostgreSQL 15's UNIQUE NULLS NOT DISTINCT). A foreign key enforces referential integrity: a value in the child column must exist in the referenced parent column, with configurable ON DELETE and ON UPDATE actions (CASCADE, SET NULL, RESTRICT, NO ACTION).

Practical details interviewers probe: in MySQL InnoDB the primary key is the clustered index, meaning the table data is physically organised by it, so a random primary key like UUIDv4 scatters inserts across pages and hurts write performance, which is why auto-increment integers or time-ordered UUIDv7 are preferred. PostgreSQL tables are heaps, so this specific concern does not apply, though index size still does. A crucial production gotcha: MySQL automatically creates an index on the child column of a foreign key, but PostgreSQL does NOT, so a DELETE on the parent table triggers a sequential scan of the child per row unless you add the index yourself, a classic cause of mysterious slow deletes and lock pile-ups. Also know the natural-versus-surrogate key debate: natural keys (PAN, email) look attractive but change in real life, so surrogate keys plus a unique constraint on the natural candidate is the standard pattern.

CREATE TABLE customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       VARCHAR(254) NOT NULL,
  pan         CHAR(10),
  CONSTRAINT uq_customers_email UNIQUE (email)
);

CREATE TABLE orders (
  order_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL
    REFERENCES customers (customer_id) ON DELETE RESTRICT,
  amount      NUMERIC(12,2) NOT NULL CHECK (amount >= 0)
);

-- PostgreSQL does NOT index FK columns automatically. Do it yourself:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Answer

COUNT(*) counts rows, full stop, including rows where every column is NULL. COUNT(column) counts rows where that column is NOT NULL. COUNT(DISTINCT column) counts unique non-NULL values.

The differences are not academic: on a table where delivered_at is NULL for undelivered orders, COUNT(*) gives total orders while COUNT(delivered_at) gives delivered orders, and candidates who conflate them produce silently wrong business metrics. Performance-wise, COUNT(*) is not slower than COUNT(1) or COUNT(id); all mainstream optimisers treat COUNT(*) as 'count rows' and will satisfy it from the narrowest available index. In MySQL InnoDB, COUNT(*) on a large table is genuinely expensive because InnoDB must scan an index to get a transaction-consistent count (unlike the old MyISAM cached row count); for approximate dashboard numbers, information_schema.TABLES.TABLE_ROWS or an application-maintained counter is standard.

In PostgreSQL, exact COUNT(*) also scans, and the common fast estimate is reltuples from pg_class, kept fresh by autovacuum's ANALYZE. COUNT(DISTINCT x) is far heavier than COUNT(x) because it must deduplicate, typically via sort or hash; on very large datasets analytics engines and PostgreSQL extensions offer approximate alternatives (HyperLogLog via the postgres-hll extension, or APPROX_COUNT_DISTINCT in warehouses like BigQuery and Snowflake), a nice detail to mention in data-engineer interviews. Finally, COUNT over a window (COUNT(*) OVER (PARTITION BY ...)) counts per partition without collapsing rows, which bridges into window-function territory.

SELECT
  COUNT(*)                    AS total_orders,      -- all rows
  COUNT(delivered_at)         AS delivered_orders,  -- non-NULL only
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

-- Fast approximate row count in PostgreSQL
SELECT reltuples::BIGINT AS approx_rows
FROM pg_class
WHERE relname = 'orders';

Answer

ORDER BY sorts the final result set and accepts multiple sort keys evaluated left to right: ORDER BY city ASC, amount DESC sorts by city first, breaking ties by amount descending. Each key gets its own direction. Without ORDER BY, row order is undefined; MySQL sometimes appears to return insertion order or index order, and candidates who rely on that get burned the day the plan changes.

NULL placement differs by engine and is a favourite trivia probe: PostgreSQL treats NULL as larger than every value, so ASC puts NULLs last and DESC puts them first, and it supports explicit NULLS FIRST / NULLS LAST. MySQL treats NULL as smallest, so ASC puts NULLs first, and it does not support the NULLS FIRST syntax; the idiom is ORDER BY col IS NULL, col to force NULLs last (the boolean expression sorts 0 before 1). You can also sort by expressions (ORDER BY LOWER(name)), by SELECT aliases (legal because ORDER BY runs after SELECT), and by column position (ORDER BY 2), though positional sorting is discouraged in production code because adding a column silently changes semantics.

A performance point worth raising: if an index matches the ORDER BY (same columns, compatible direction), the engine can stream rows in order and skip an explicit sort, which matters enormously with LIMIT, an index-backed ORDER BY created_at DESC LIMIT 20 reads 20 rows instead of sorting millions. EXPLAIN shows 'Using filesort' in MySQL or a Sort node in PostgreSQL when no index helps.

-- Multiple keys, per-key direction
SELECT name, city, amount
FROM invoices
ORDER BY city ASC, amount DESC;

-- PostgreSQL: explicit NULL placement
SELECT name, last_login
FROM users
ORDER BY last_login DESC NULLS LAST;

-- MySQL idiom for the same effect
SELECT name, last_login
FROM users
ORDER BY last_login IS NULL, last_login DESC;

Answer

LIMIT n OFFSET m skips m rows and returns the next n. MySQL also accepts the shorthand LIMIT m, n, and standard SQL spells it OFFSET m ROWS FETCH FIRST n ROWS ONLY (supported by PostgreSQL and most enterprise engines). Two problems make naive OFFSET pagination a rich interview topic.

First, performance: OFFSET 100000 LIMIT 20 forces the engine to produce and discard 100,000 rows before returning 20, so page-load time grows linearly with page number. Deep pagination on a big table is effectively a partial table scan per request, and this is a real production incident pattern at listing-heavy companies (job boards, e-commerce category pages). Second, correctness: OFFSET is not stable under concurrent writes.

If a row is inserted or deleted between page 1 and page 2 requests, the boundary shifts and users see duplicated or skipped records. Both problems are solved by keyset pagination (also called cursor or seek pagination): remember the sort key of the last row served and fetch WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20. That predicate is index-navigable, so every page costs the same regardless of depth.

The trade-off is that you lose 'jump to page 47' and can only go next/previous, which is why admin panels often keep OFFSET while public infinite-scroll feeds use keyset. Always pair any LIMIT with a deterministic ORDER BY including a unique tiebreaker column, otherwise page boundaries are non-deterministic even without writes.

-- Naive: cost grows with depth, unstable under writes
SELECT id, title, created_at
FROM jobs
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

-- Keyset (seek) pagination: constant cost per page
SELECT id, title, created_at
FROM jobs
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Supporting index
CREATE INDEX idx_jobs_created_id ON jobs (created_at DESC, id DESC);

Answer

LIKE matches strings against a pattern where % matches any sequence of characters (including empty) and _ matches exactly one character. 'raz%' matches strings starting with raz; '%pay' matches strings ending with pay; '%credit%' matches substrings. To match a literal % or _ you escape it, by default with backslash in MySQL or with an explicit ESCAPE clause portably: LIKE '50\%%' or LIKE '50!%%' ESCAPE '!'. Case sensitivity differs sharply by engine: MySQL comparisons follow the column collation, and the common utf8mb4_0900_ai_ci collation is case-insensitive, so LIKE 'a%' matches 'Apple'.

PostgreSQL LIKE is case-sensitive; ILIKE is the case-insensitive variant, or you wrap both sides in LOWER(). The performance point is what separates candidates: a B-tree index can serve LIKE 'raz%' because the prefix bounds a contiguous index range, but LIKE '%pay' or '%credit%' cannot use a normal B-tree at all, forcing a full scan. In PostgreSQL there are two extra wrinkles: if the database uses a non-C collation you need the text_pattern_ops operator class on the index for prefix LIKE to work, and for substring search the pg_trgm extension with a GIN index makes LIKE '%credit%' genuinely fast.

In MySQL, substring search at scale means a FULLTEXT index with MATCH ... AGAINST or an external engine like Elasticsearch or OpenSearch. Interviewers often phrase this as 'search by phone number suffix is slow, why?', and the expected answer is the leading wildcard plus the reversed-column-index trick (store the reversed string, index it, search by reversed prefix).

-- Prefix search: index-friendly
SELECT * FROM companies WHERE name LIKE 'Raz%';

-- Substring search: full scan on a plain B-tree
SELECT * FROM companies WHERE name LIKE '%pay%';

-- PostgreSQL: make substring search fast with trigrams
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_companies_name_trgm
  ON companies USING GIN (name gin_trgm_ops);

-- Case-insensitive search
SELECT * FROM companies WHERE name ILIKE 'raz%';  -- PostgreSQL
SELECT * FROM companies WHERE LOWER(name) LIKE 'raz%';  -- portable

Answer

All three can answer 'customers who placed an order', but they differ in semantics and edge cases more than raw speed on modern engines. IN (subquery) checks membership of a value in the subquery's result set. EXISTS (correlated subquery) checks whether at least one matching row exists, stopping at the first hit.

An INNER JOIN combines the tables, which changes row multiplicity: joining customers to orders returns one row per order, so you must add DISTINCT or aggregate to get one row per customer, and that extra dedup step is pure waste when you only wanted existence. Semantically, EXISTS and IN are semi-joins; use them when you need filtering, not data from the other table. On the optimiser front, PostgreSQL and MySQL 8 both transform IN and EXISTS into the same semi-join plan in most cases, so the old folk wisdom 'EXISTS is always faster than IN' is outdated; measure with EXPLAIN rather than reciting rules.

The one hard rule that survives: for negation, prefer NOT EXISTS over NOT IN, because NOT IN returns zero rows if the subquery yields even a single NULL, a silent correctness bug, whereas NOT EXISTS handles NULLs sanely. Also mention that IN with a literal list (IN (1,2,3)) is a different, unproblematic construct, and that very long literal IN lists (thousands of ids from application code) should become a temporary table or VALUES join instead, both for plan quality and to avoid parser limits.

-- Semi-join: customers with at least one delivered order
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'DELIVERED'
);

-- Equivalent IN form (same plan in PG / MySQL 8)
SELECT customer_id, name
FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE status = 'DELIVERED'
);

-- Anti-join: ALWAYS NOT EXISTS, never NOT IN (NULL trap)
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Answer

Both stack the results of two or more SELECTs with compatible column counts and types. UNION removes duplicate rows across the combined result; UNION ALL keeps everything. The deduplication is the whole story: UNION must sort or hash the entire combined set to eliminate duplicates, which on large results costs significant memory and CPU and can spill to disk, while UNION ALL is a simple concatenation that streams.

The professional habit is to write UNION ALL by default and switch to UNION only when duplicate removal is a stated requirement; many code reviewers flag bare UNION as a probable performance bug. Note that UNION deduplicates the entire result, including rows that were unique within their own branch but identical across branches, and that NULLs count as equal for this dedup. Column names in the result come from the first SELECT, and ORDER BY applies to the combined result only when written at the end (to sort within a branch, wrap that branch in a subquery, though the outer result order remains whatever the final ORDER BY says).

The related set operators INTERSECT and EXCEPT (called MINUS in Oracle) are supported by PostgreSQL and by MySQL from 8.0.31, and each has an ALL variant. A practical use of UNION ALL worth naming in interviews: combining current and archive tables (orders plus orders_2024_archive) behind a view, which is a common pattern in Indian enterprises that hand-roll archival instead of using partitioning.

-- Deduplicated: expensive on large sets
SELECT email FROM newsletter_subscribers
UNION
SELECT email FROM webinar_registrations;

-- Keep everything: streams, no dedup cost
SELECT 'subscriber' AS source, email FROM newsletter_subscribers
UNION ALL
SELECT 'webinar' AS source, email FROM webinar_registrations
ORDER BY email;  -- applies to the combined result

-- MySQL 8.0.31+ / PostgreSQL: other set operators
SELECT email FROM newsletter_subscribers
INTERSECT
SELECT email FROM webinar_registrations;

Answer

These are the four working categories of SQL statements. DDL (Data Definition Language) defines schema: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, TRUNCATE. DML (Data Manipulation Language) works with rows: SELECT, INSERT, UPDATE, DELETE, and MERGE where supported.

DCL (Data Control Language) manages permissions: GRANT and REVOKE. TCL (Transaction Control Language) manages transactions: BEGIN/START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION ISOLATION LEVEL. The classification question itself is screening-round filler, but two follow-ups have real depth.

First: is TRUNCATE DDL or DML? It is classified as DDL because it deallocates data pages rather than deleting rows one by one, and in MySQL it implicitly commits and resets AUTO_INCREMENT, cannot be rolled back, and does not fire DELETE triggers. In PostgreSQL, unusually, TRUNCATE is transactional and can be rolled back, a genuine dialect difference worth naming.

Second: MySQL historically performed an implicit commit before every DDL statement, so running an ALTER TABLE in the middle of a transaction silently committed your pending changes; MySQL 8.0 added atomic DDL (the statement itself is crash-safe) but DDL still cannot live inside a multi-statement transaction. PostgreSQL supports fully transactional DDL, letting migration tools wrap a whole schema change plus data backfill in one transaction and roll it back on failure, which is one of the practical reasons migration-heavy teams prefer PostgreSQL.

Answer

CHAR(n) is fixed-length and space-padded, appropriate only for truly fixed-width codes like CHAR(10) for PAN or CHAR(2) for state codes. VARCHAR(n) stores variable-length strings up to n characters; in MySQL the limit is enforced and part of the row format, while in PostgreSQL VARCHAR without a length and TEXT are literally the same type internally, and the community default is TEXT plus a CHECK constraint when you need a limit. In MySQL, TEXT types cannot have DEFAULT values, are stored partly off-page, and any index on them needs a prefix length (INDEX (body(100))), so VARCHAR is preferred for anything you filter or sort on.

For numbers: DECIMAL(p,s) (alias NUMERIC) is exact fixed-point arithmetic; FLOAT and DOUBLE are IEEE-754 binary floating point, which cannot represent most decimal fractions exactly. 0.1 + 0.2 stored as DOUBLE is 0.30000000000000004, so summing paise across millions of transactions drifts, and equality comparisons randomly fail. Every payments schema in India (Razorpay, PhonePe, banks) stores money either as DECIMAL(12,2)-style exact types or as integer paise in a BIGINT, never FLOAT; an interviewer hearing you say 'FLOAT for the amount column' will fail the round on the spot. Other type-choice points that earn credit: use BIGINT for ids from day one (INT overflows at 2.1 billion, a real incident category), TIMESTAMP WITH TIME ZONE (timestamptz) in PostgreSQL for event times, DATETIME vs TIMESTAMP range differences in MySQL (TIMESTAMP ends in 2038 unless the deployment has 64-bit timestamps), and UUID stored as the native uuid type in PG or BINARY(16) in MySQL rather than VARCHAR(36).

-- Money: exact types only
CREATE TABLE payments (
  payment_id  BIGINT PRIMARY KEY,
  amount      DECIMAL(12,2) NOT NULL,   -- or amount_paise BIGINT
  pan         CHAR(10),
  notes       TEXT
);

-- The float drift demo interviewers expect you to know
SELECT 0.1::FLOAT8 + 0.2::FLOAT8;   -- 0.30000000000000004 (PostgreSQL)
SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE);  -- MySQL, same drift

SELECT 0.1::NUMERIC + 0.2::NUMERIC; -- 0.3 exactly

Answer

DELETE is row-level DML: it removes rows matching a WHERE clause (or all rows without one), fires row-level triggers, writes undo/WAL per row, respects foreign keys per row, and is fully transactional in both MySQL and PostgreSQL. TRUNCATE is DDL: it deallocates the table's data pages in one operation, which makes it dramatically faster on large tables, resets AUTO_INCREMENT in MySQL (and RESTART IDENTITY optionally in PostgreSQL), skips DELETE triggers, and in MySQL cannot run if any foreign key references the table (even an empty child) unless you drop the FK first; PostgreSQL requires TRUNCATE ... CASCADE to include referencing tables.

Notably, TRUNCATE is transactional and rollback-able in PostgreSQL but auto-commits in MySQL. DROP removes the table itself, its data, indexes, constraints, triggers, and privileges. The production judgement interviewers are testing: deleting tens of millions of rows with a single DELETE bloats the undo log or WAL, holds locks for the whole statement, can stall replication for minutes, and on MySQL can blow past innodb_buffer_pool efficiency.

The standard pattern is batched deletes, DELETE ... WHERE ... LIMIT 5000 in a loop with a short sleep, watching replica lag between batches (MySQL supports LIMIT on DELETE; PostgreSQL uses a ctid or id-range batching pattern instead).

If you are removing everything, TRUNCATE. If you are removing most of a huge table, it is often cheaper to copy the survivors into a new table, swap names, and drop the old one.

-- MySQL: batched delete to protect replicas and undo log
DELETE FROM audit_logs
WHERE created_at < NOW() - INTERVAL 180 DAY
LIMIT 5000;   -- run in a loop until 0 rows affected

-- PostgreSQL: batched delete via id ranges
DELETE FROM audit_logs
WHERE id IN (
  SELECT id FROM audit_logs
  WHERE created_at < NOW() - INTERVAL '180 days'
  LIMIT 5000
);

-- Wipe a table fast, restarting the sequence
TRUNCATE TABLE staging_imports RESTART IDENTITY;  -- PostgreSQL

Answer

A non-correlated subquery is self-contained: it can run once, independently, and its result feeds the outer query, for example WHERE amount > (SELECT AVG(amount) FROM orders). A correlated subquery references columns from the outer query, so logically it re-executes per outer row: WHERE amount > (SELECT AVG(o2.amount) FROM orders o2 WHERE o2.customer_id = o.customer_id). Subqueries can appear in four positions, and naming them precisely scores points: scalar subqueries in the SELECT list or comparisons (must return at most one row and one column, otherwise you get 'more than one row returned by a subquery used as an expression' in PostgreSQL or error 1242 in MySQL), table subqueries (derived tables) in FROM, which MySQL requires you to alias, predicate subqueries with IN/EXISTS/ANY/ALL in WHERE, and row subqueries compared as tuples.

The performance narrative has evolved and interviewers know it: naive correlated subqueries were historically O(N x M) disasters, but modern optimisers frequently decorrelate them into joins; MySQL 8 materialises derived tables or merges them into the outer query, and PostgreSQL flattens most IN/EXISTS into semi-joins. Still, a correlated scalar subquery in the SELECT list of a big scan is a reliable way to write an accidental N+1 inside a single query, and the deliberate rewrite, turning it into a LEFT JOIN against a pre-aggregated derived table or a window function, remains one of the most common 'optimise this query' exercises in Indian interviews.

-- Correlated scalar subquery: readable, but per-row work
SELECT o.order_id, o.amount,
  (SELECT AVG(o2.amount) FROM orders o2
   WHERE o2.customer_id = o.customer_id) AS customer_avg
FROM orders o;

-- Decorrelated rewrite: aggregate once, join once
SELECT o.order_id, o.amount, ca.customer_avg
FROM orders o
JOIN (
  SELECT customer_id, AVG(amount) AS customer_avg
  FROM orders
  GROUP BY customer_id
) ca ON ca.customer_id = o.customer_id;

-- Window-function rewrite: single pass, no join
SELECT order_id, amount,
       AVG(amount) OVER (PARTITION BY customer_id) AS customer_avg
FROM orders;

Answer

CASE is SQL's inline conditional expression, usable anywhere an expression is legal: SELECT, WHERE, ORDER BY, GROUP BY, and inside aggregate functions. The searched form (CASE WHEN condition THEN result ... ELSE default END) evaluates conditions top to bottom and returns the first match; without ELSE, a non-match yields NULL, a silent behaviour that causes real bugs in reports.

The simple form (CASE status WHEN 'PAID' THEN ... END) compares one expression against values, but beware: it uses equality, so it can never match NULL (CASE col WHEN NULL always fails; use the searched form with IS NULL). Conditional aggregation, putting a CASE inside SUM or COUNT, is arguably the highest-value basic pattern in analytics interviews because it pivots rows into columns in one pass: SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END) counts delivered orders while other CASE branches count cancellations in the same scan, producing a one-row-per-group summary without multiple queries or self-joins.

COUNT(CASE WHEN cond THEN 1 END) works too because COUNT skips the NULLs from the missing ELSE. PostgreSQL offers the cleaner FILTER clause, COUNT(*) FILTER (WHERE status = 'DELIVERED'), which is standard SQL but not supported by MySQL, so the CASE form remains the portable idiom. Also mention CASE in ORDER BY for custom sort orders (pin featured rows first) and in UPDATE SET for branching updates in a single statement. Interviewers frequently dress this up as 'produce a month-wise delivered/cancelled/returned matrix', which is exactly one GROUP BY month plus three conditional aggregates.

-- Pivot order statuses into columns in one scan
SELECT
  DATE_FORMAT(created_at, '%Y-%m')                          AS month,
  SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END)     AS delivered,
  SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END)     AS cancelled,
  SUM(CASE WHEN status = 'RETURNED'  THEN amount ELSE 0 END) AS refund_value
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m');

-- PostgreSQL FILTER: same idea, cleaner syntax
SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) FILTER (WHERE status = 'DELIVERED') AS delivered,
       COUNT(*) FILTER (WHERE status = 'CANCELLED') AS cancelled
FROM orders
GROUP BY 1;

-- Custom sort: featured jobs first
SELECT * FROM jobs
ORDER BY CASE WHEN is_featured THEN 0 ELSE 1 END, created_at DESC;

Answer

Date handling is where dialect differences bite hardest in interviews that let you choose your engine. Current time: NOW() works in both; PostgreSQL distinguishes now() (transaction start time, constant within a transaction) from clock_timestamp() (wall clock), while MySQL's NOW() is statement start time and SYSDATE() is call time. Truncation to a period: PostgreSQL uses DATE_TRUNC('month', created_at), the cleanest tool for cohort and month-over-month queries; MySQL has no DATE_TRUNC, so the idioms are DATE_FORMAT(created_at, '%Y-%m-01') or LAST_DAY arithmetic.

Arithmetic: PostgreSQL uses interval literals, created_at >= NOW() - INTERVAL '30 days'; MySQL spells it INTERVAL 30 DAY (no quotes, singular) with DATE_ADD/DATE_SUB equivalents. Differences between dates: MySQL DATEDIFF(a, b) returns days as a minus b, while PostgreSQL simply subtracts dates to get an integer of days, or subtracts timestamps to get an interval, and uses AGE() for calendar-aware differences. Extraction: EXTRACT(YEAR FROM col) is standard and works in both; MySQL adds YEAR(), MONTH(), DAYOFWEEK() shorthands.

Formatting: MySQL DATE_FORMAT with %-patterns versus PostgreSQL TO_CHAR with 'YYYY-MM' patterns. The production advice worth volunteering: store timestamps in UTC (timestamptz in PostgreSQL, which stores UTC and converts on display per the TimeZone setting; MySQL TIMESTAMP also converts via time_zone, but DATETIME stores naive wall time with no zone), convert to IST only at the presentation layer, and never wrap the indexed timestamp column in a function inside WHERE, because DATE(created_at) = '2026-08-11' kills index usage; write a half-open range instead: created_at >= '2026-08-11' AND created_at < '2026-08-12'.

-- PostgreSQL: monthly cohorts
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*)
FROM signups
WHERE created_at >= NOW() - INTERVAL '6 months'
GROUP BY 1 ORDER BY 1;

-- MySQL equivalent
SELECT DATE_FORMAT(created_at, '%Y-%m-01') AS month, COUNT(*)
FROM signups
WHERE created_at >= NOW() - INTERVAL 6 MONTH
GROUP BY 1 ORDER BY 1;

-- Sargable date filter (index-friendly, both engines)
SELECT * FROM orders
WHERE created_at >= '2026-08-11 00:00:00'
  AND created_at <  '2026-08-12 00:00:00';
-- NOT: WHERE DATE(created_at) = '2026-08-11'  (index ignored)

Answer

Beyond PRIMARY KEY, UNIQUE, and FOREIGN KEY, the toolkit is NOT NULL, DEFAULT, and CHECK. NOT NULL should be your default posture for every column unless absence is genuinely meaningful; nullable-everything schemas push NULL handling into every downstream query. DEFAULT supplies a value when the INSERT omits the column, and can be a function: DEFAULT NOW() for created_at, DEFAULT gen_random_uuid() in PostgreSQL.

CHECK enforces a boolean predicate per row: CHECK (amount >= 0), CHECK (status IN ('PENDING','PAID','FAILED')), CHECK (starts_at < ends_at). A dialect landmine worth stating explicitly: MySQL parsed but silently IGNORED CHECK constraints for two decades; enforcement only arrived in MySQL 8.0.16, so legacy MySQL schemas cannot rely on their CHECKs, and this is a known trick question. For foreign keys, the referential actions are ON DELETE CASCADE (delete children with the parent, dangerous on deep hierarchies where one DELETE can silently remove millions of rows and hold locks), ON DELETE SET NULL (orphan the child reference, requires a nullable column), ON DELETE RESTRICT / NO ACTION (block the delete; in PostgreSQL NO ACTION is deferrable to transaction end while RESTRICT checks immediately).

Production teams frequently choose RESTRICT everywhere and handle cleanup explicitly in application code, precisely to avoid surprise cascades. Also worth knowing: PostgreSQL supports DEFERRABLE INITIALLY DEFERRED constraints, letting you insert mutually referencing rows within a transaction and validate at COMMIT, and both engines let you add NOT VALID / later-validated constraints to avoid long locks when constraining existing big tables.

CREATE TABLE subscriptions (
  subscription_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(user_id) ON DELETE RESTRICT,
  plan       VARCHAR(20) NOT NULL
             CHECK (plan IN ('FREE','GOLD','PLATINUM')),
  price      NUMERIC(10,2) NOT NULL CHECK (price >= 0),
  starts_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ends_at    TIMESTAMPTZ,
  CHECK (ends_at IS NULL OR ends_at > starts_at)
);

-- Constrain an existing large table without a long lock (PostgreSQL)
ALTER TABLE payments ADD CONSTRAINT chk_amount_positive
  CHECK (amount >= 0) NOT VALID;
ALTER TABLE payments VALIDATE CONSTRAINT chk_amount_positive;

Answer

This is the single most re-used screening question in Indian SQL interviews, and the grading is entirely about edge cases: duplicate salaries and the case where no second-highest exists. The naive LIMIT 1 OFFSET 1 on ORDER BY salary DESC fails on duplicates (two people at the top salary make the second row the same value, not the second distinct value) and returns an empty set rather than NULL when the table has one distinct salary. The scalar-subquery form, SELECT MAX(salary) WHERE salary < (SELECT MAX(salary)), handles both: MAX over an empty set returns NULL, and the comparison naturally works on distinct values.

The modern answer interviewers now expect is DENSE_RANK: rank salaries descending with DENSE_RANK() (which does not skip ranks on ties, unlike RANK) and pick rank 2; this generalises immediately to 'Nth highest' and to 'top N per department' by adding PARTITION BY department_id, which is the standard follow-up. If asked for LIMIT/OFFSET anyway, the corrected version is SELECT DISTINCT salary ORDER BY salary DESC LIMIT 1 OFFSET 1, wrapped in a subselect if the empty-set-versus-NULL distinction matters. State your assumptions out loud: whether 'second highest' means second distinct value (usual intent) or second row, and what should happen with fewer than two distinct salaries. Handling those two edge cases unprompted is what separates a pass from a fail on this question.

-- Portable scalar-subquery form: NULL when absent, tie-safe
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Window form: generalises to Nth highest and per-department
SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;

-- Follow-up: top 2 salaries per department
SELECT department_id, salary
FROM (
  SELECT department_id, salary,
         DENSE_RANK() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk <= 2;

Answer

Finding duplicates is a GROUP BY ... HAVING COUNT(*) > 1 on the columns that define 'duplicate' (say email, or the pair phone + email). Deleting them while keeping one survivor per group is the interesting part and a very common live exercise.

The cleanest modern approach uses ROW_NUMBER(): partition by the duplicate key, order by a survivor rule (keep lowest id, or latest updated_at), and delete everything with row number greater than 1. In PostgreSQL you cannot DELETE directly from a CTE result, so you join the CTE back on the primary key: DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1). MySQL 8 supports the same CTE shape, but with a twist: MySQL raises error 1093 if you reference the target table in a direct subquery of a DELETE, and the standard workaround is wrapping the subquery in one more derived table so it materialises.

The old-school MySQL answers still get asked: the self-join delete (DELETE u1 FROM users u1 JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id) and the sledgehammer ALTER IGNORE trick from 5.x which no longer exists. Two production notes that impress: on a large table, do the delete in batches (loop with LIMIT) to avoid replica lag and long locks; and immediately after cleanup, add a UNIQUE constraint on the key so duplicates cannot return, ideally taking a brief write pause or using PostgreSQL's CREATE UNIQUE INDEX CONCURRENTLY to avoid blocking writes during the build.

-- Find duplicate emails
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- PostgreSQL: keep the lowest id per email
DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY id
    ) AS rn
    FROM users
  ) ranked
  WHERE rn > 1
);

-- MySQL 8: self-join form (avoids error 1093 entirely)
DELETE u1 FROM users u1
JOIN users u2
  ON u1.email = u2.email AND u1.id > u2.id;

-- Prevent recurrence without blocking writes (PostgreSQL)
CREATE UNIQUE INDEX CONCURRENTLY uq_users_email ON users (email);

Answer

A view is a named, stored SELECT statement that behaves like a virtual table: querying the view runs (or merges) the underlying query against live data, so views hold no data of their own (unlike materialized views). The three production uses worth naming: abstraction (hide a gnarly five-way join behind clean columns), security (grant analysts SELECT on a view exposing only non-PII columns instead of the base table, a pattern every Indian company doing DPDP-Act compliance work uses), and backward compatibility (after renaming columns or splitting a table, a view with the old shape keeps legacy readers working). A view is updatable, meaning you can INSERT/UPDATE/DELETE through it, only when the engine can map each view row to exactly one base-table row: single table, no DISTINCT, no GROUP BY or aggregates, no UNION, no window functions.

MySQL additionally documents that the view must not use certain constructs in the select list, and provides WITH CHECK OPTION to reject writes that would produce rows the view itself cannot see (inserting status = 'DELETED' through a view defined WHERE status = 'ACTIVE'). PostgreSQL auto-updates simple views under the same single-table rules and lets you make any complex view writable via INSTEAD OF triggers or rules. Performance truth interviewers check: a view is not automatically slower than its query, the optimiser usually merges it inline, but stacking views on views hides complexity until someone writes a six-view join that nobody can EXPLAIN, so most teams cap view nesting depth in review.

-- Security view: analysts never touch PII columns
CREATE VIEW analyst_users AS
SELECT user_id, city, signup_source, created_at
FROM users
WHERE deleted_at IS NULL;

GRANT SELECT ON analyst_users TO analyst_role;

-- Updatable view with a write guard
CREATE VIEW active_jobs AS
SELECT job_id, title, status FROM jobs WHERE status = 'ACTIVE'
WITH CHECK OPTION;

-- This now fails instead of silently vanishing from the view:
-- UPDATE active_jobs SET status = 'PAUSED' WHERE job_id = 42;

Answer

All three auto-generate sequential integer ids, but the mechanics differ. MySQL uses AUTO_INCREMENT on an integer column; each table has one counter, retrievable per-connection after insert via LAST_INSERT_ID(). Historically the counter lived only in memory and could reuse values after a crash-restart; MySQL 8 made it persistent.

PostgreSQL's SERIAL is legacy shorthand that creates a sequence and wires the column default to nextval(); the modern (SQL-standard) syntax since PostgreSQL 10 is GENERATED ALWAYS AS IDENTITY (or BY DEFAULT), which is what new schemas should use, and interviewers now expect you to say so. Applications get the generated id back with INSERT ... RETURNING id in PostgreSQL, a round-trip saver MySQL lacked until it landed in MariaDB; on MySQL you call LAST_INSERT_ID().

The gaps question is the real test: sequences are non-transactional by design, a rolled-back INSERT permanently consumes its number, and INSERT ... ON DUPLICATE KEY UPDATE and bulk inserts in InnoDB can burn ids in batches (controlled by innodb_autoinc_lock_mode, default 2 in MySQL 8, which trades contiguity for concurrency). So gaps are normal and guaranteed; any design that requires gap-free numbering (Indian GST invoice serial numbers are the canonical example) must not use auto-increment, and instead uses a counter table row updated inside the same transaction as the insert, accepting the serialisation bottleneck that implies. Also flag exhaustion: INT tops out at ~2.1 billion, and migrating a hot table's id column from INT to BIGINT under load is a notorious multi-week project, so start with BIGINT.

-- Modern PostgreSQL
CREATE TABLE invoices (
  invoice_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount NUMERIC(12,2) NOT NULL
);
INSERT INTO invoices (amount) VALUES (4999.00) RETURNING invoice_id;

-- MySQL
CREATE TABLE invoices (
  invoice_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  amount DECIMAL(12,2) NOT NULL
);
INSERT INTO invoices (amount) VALUES (4999.00);
SELECT LAST_INSERT_ID();

-- Gap-free legal numbering: counter row, same transaction
UPDATE invoice_counters SET last_no = last_no + 1 WHERE fy = '2026-27';
SELECT last_no FROM invoice_counters WHERE fy = '2026-27';

Answer

When a query has GROUP BY, every column in the SELECT list must be either inside an aggregate function or listed in GROUP BY, because after grouping there is one output row per group and the engine must know which single value to show for each column. SELECT customer_id, city, COUNT(*) FROM orders GROUP BY customer_id is ambiguous about city if a customer ordered from two cities. PostgreSQL rejects this outright with 'column "orders.city" must appear in the GROUP BY clause or be used in an aggregate function'.

MySQL's history here is a classic interview probe: before 5.7, MySQL permitted the query and returned an arbitrary value from the group, a legendary source of silently wrong reports; since 5.7, the ONLY_FULL_GROUP_BY SQL mode is on by default and MySQL raises error 1055 like the standard requires. Some legacy Indian codebases explicitly disable the mode to keep old queries limping along, which you should call out as debt, not configuration. There is one principled exception: if you GROUP BY a table's primary key, every other column of that table is 'functionally dependent' on the group key, and both PostgreSQL and MySQL 8 accept them ungrouped. Fix options when you hit the error: add the column to GROUP BY (changes grouping semantics, often wrong), aggregate it (MAX(city), or MySQL's ANY_VALUE(city) when you genuinely do not care), or restructure with a window function or a join back to the detail row you actually want, which is usually the correct answer for 'show each customer's latest order city'.

-- Rejected by PostgreSQL and by MySQL 5.7+ (error 1055)
SELECT customer_id, city, COUNT(*)
FROM orders
GROUP BY customer_id;

-- Legal: grouping by the PK makes other columns determined
SELECT c.customer_id, c.name, COUNT(o.order_id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;  -- name is functionally dependent on PK

-- 'Latest city per customer' done properly
SELECT customer_id, city
FROM (
  SELECT customer_id, city,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

Answer

A self join joins a table to itself, using two aliases so the engine treats them as independent row sources. It is the natural tool whenever rows of one table relate to other rows of the same table. The canonical example is an employees table with a manager_id column referencing employees.employee_id: SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON m.employee_id = e.manager_id lists everyone with their manager, and the LEFT JOIN matters so the CEO (manager_id NULL) is not dropped.

Other classic self-join problems that appear verbatim in interviews: finding pairs of users with the same phone number (join on phone with u1.id < u2.id to avoid mirrored and self pairs, a detail graders explicitly look for), comparing consecutive rows before window functions existed (join on t2.day = t1.day + 1, the basis of the famous 'weather warmer than yesterday' question), and detecting overlapping bookings (join intervals on a.start < b.end AND b.start < a.end AND a.id <> b.id). The practical caveats: a self join on a non-selective key can explode combinatorially (a phone number shared by 500 fake accounts produces ~125,000 pairs), so deduplicate or aggregate first when keys are hot; and for chains of arbitrary depth (manager of manager of manager), a fixed number of self joins cannot express the traversal, which is the cue to escalate to a recursive CTE, usually the interviewer's intended follow-up.

-- Employees with their managers (keep the CEO via LEFT JOIN)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.manager_id;

-- Duplicate phone pairs, each pair once, no self-pairs
SELECT u1.id, u2.id, u1.phone
FROM users u1
JOIN users u2
  ON u1.phone = u2.phone
 AND u1.id < u2.id;

-- Days warmer than the previous day
SELECT t.record_date
FROM temperatures t
JOIN temperatures y
  ON y.record_date = t.record_date - INTERVAL '1 day'
WHERE t.temp_c > y.temp_c;

Answer

Normalization organises tables to eliminate redundancy so every fact lives in exactly one place. Practically: 1NF means atomic values, no comma-separated phone lists in a column and no repeating column groups (phone1, phone2, phone3); violations become child tables. 2NF applies to composite keys: every non-key column must depend on the whole key, so in order_items keyed by (order_id, product_id), product_name depends only on product_id and must move to products. 3NF removes transitive dependencies: if employees stores department_id and also department_name, the name depends on department_id, not on the employee, so it moves to departments. BCNF tightens edge cases where a non-trivial dependency's determinant is not a candidate key; one sentence on it is enough unless the role is data-modelling heavy.

The payoff of 3NF is update integrity: renaming a department is one UPDATE, not a sweep across millions of rows that can partially fail. The cost is joins, and that is where denormalization enters as a deliberate, measured trade: read-heavy paths cache derived or copied values, an orders.customer_city snapshot at order time (which is arguably not even denormalization, since historical shipping city is a distinct fact from current city), counter columns like posts.like_count maintained by triggers or application code instead of COUNT(*) on every page view, and star-schema warehouses that flatten dimensions for scan speed. The interview-ready framing: normalize OLTP by default for correctness, denormalize specific hot read paths with a documented mechanism that keeps the copy consistent, and treat every denormalized field as a cache with an owner and an invalidation story.

Answer

All three are ranking window functions that number rows within a partition according to an ORDER BY, without collapsing the rows the way GROUP BY does. The difference is entirely about ties. ROW_NUMBER() assigns unique consecutive numbers 1,2,3,4 even when the ordering values are equal, breaking ties arbitrarily unless you add a deterministic tiebreaker to the ORDER BY.

RANK() gives tied rows the same rank and then skips: two rows tied at rank 1 are followed by rank 3 (Olympic-style ranking). DENSE_RANK() gives ties the same rank without skipping: 1,1,2,3. Choosing the wrong one changes business answers: 'top 3 salaries' with RANK can return two rows and stop (ranks 1,1,3 with rank <= 3 matching 3 rows but tied groups can also return more than 3 rows), while DENSE_RANK <= 3 returns everyone in the top three distinct salary values, and ROW_NUMBER <= 3 returns exactly three rows with an arbitrary tie cut.

Interviewers deliberately construct tie data to see whether you ask 'what should happen on ties?', which is itself the senior signal. Since window functions are computed during SELECT, you cannot filter on them in WHERE; wrap the query in a derived table or CTE and filter outside, or use the QUALIFY clause in engines that have it (Snowflake, BigQuery, DuckDB; neither MySQL nor PostgreSQL supports QUALIFY). Window functions landed in MySQL only in 8.0, so any answer claiming to support MySQL 5.7 must fall back to correlated subqueries or session-variable tricks, worth mentioning because plenty of Indian enterprises still run 5.7 forks.

SELECT employee_id, department_id, salary,
  ROW_NUMBER() OVER w AS row_num,   -- 1,2,3,4 (ties broken arbitrarily)
  RANK()       OVER w AS rnk,       -- 1,1,3,4 (skips after ties)
  DENSE_RANK() OVER w AS dense_rnk  -- 1,1,2,3 (no skips)
FROM employees
WINDOW w AS (PARTITION BY department_id ORDER BY salary DESC);

-- Filter on a window function: must wrap it
SELECT * FROM (
  SELECT e.*, DENSE_RANK() OVER (
    PARTITION BY department_id ORDER BY salary DESC
  ) AS dr
  FROM employees e
) t
WHERE dr <= 3;   -- top 3 distinct salary levels per department

Answer

A window frame defines which rows, relative to the current row, an aggregate window function sees. The syntax is ROWS BETWEEN <start> AND <end> with bounds like UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING. SUM(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is a running total; AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) is a 7-day moving average.

The gotcha that fails candidates: when you write an ORDER BY inside OVER() without specifying a frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE treats all peer rows (equal ORDER BY values) as one unit. With duplicate dates, the 'running total' jumps in chunks, including all rows for the current date at once, which differs from the row-by-row total most people intend. The fix is to spell out ROWS explicitly or make the ordering key unique.

RANGE with numeric offsets (RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW) defines value-based windows, correct for time-based moving averages over irregular data where '7 preceding rows' is not '7 days'; PostgreSQL supports RANGE with offsets from version 11, MySQL 8 supports RANGE offsets for numeric/temporal types too. There is also GROUPS mode (peer groups as units, PostgreSQL) and frame exclusion (EXCLUDE CURRENT ROW, PostgreSQL). Performance note: each distinct window specification can force its own sort, so reuse a named WINDOW clause when several functions share partitioning, and expect an EXPLAIN to show WindowAgg (PG) or a windowing step after filesort (MySQL).

SELECT day, amount,
  -- Running total: explicit ROWS frame avoids the RANGE-peers trap
  SUM(amount) OVER (
    ORDER BY day, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  -- 7-row moving average
  AVG(amount) OVER (
    ORDER BY day, id
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma_7,
  -- True 7-DAY moving sum over irregular data (PG 11+, MySQL 8)
  SUM(amount) OVER (
    ORDER BY day
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS weekly_sum
FROM daily_sales;

Answer

LAG(col, n, default) returns col from the row n positions earlier within the partition's ORDER BY; LEAD looks forward. The third argument supplies a value when the offset runs off the edge of the partition (otherwise NULL), and remembering it exists is a small but reliable senior signal. The archetypal use is month-over-month growth: aggregate to monthly revenue in a CTE, then LAG(revenue) OVER (ORDER BY month) gives last month's number on the current row, and growth is (revenue - prev) / prev, guarded with NULLIF against a zero previous month, times 100 for percentage.

Because LAG is evaluated after grouping, you can layer it directly on the aggregate query in one statement. Other stock problems: time between events per user (LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at), subtract, then histogram the gaps), detecting status transitions (compare status to LAG(status) and keep rows where they differ, which compresses an event log into change points), and flagging price changes against the previous reading. LAG versus a self join on month-1 is a favourite discussion: the self join breaks when months are missing (January to March comparison silently disappears), while LAG compares to the previous EXISTING row, which may or may not be what the business wants, so the fully correct answer generates a calendar spine (generate_series in PostgreSQL, a recursive CTE in MySQL) and left-joins revenue onto it before applying LAG. That distinction, previous row versus previous period, is exactly what strong analytics interviewers dig for.

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
         / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2
       ) AS mom_growth_pct
FROM monthly
ORDER BY month;

-- Status change points from an event log
SELECT * FROM (
  SELECT order_id, status, updated_at,
         LAG(status) OVER (PARTITION BY order_id
                           ORDER BY updated_at) AS prev_status
  FROM order_events
) t
WHERE status IS DISTINCT FROM prev_status;  -- PG; MySQL: NOT (status <=> prev_status)

Answer

A CTE (common table expression) is a named subquery declared with WITH before the main statement, readable top-to-bottom like a pipeline: WITH monthly AS (...), ranked AS (SELECT ... FROM monthly) SELECT ... FROM ranked.

Functionally, a non-recursive CTE is equivalent to a derived table; the wins are readability, reuse (reference the same CTE twice instead of pasting the subquery), and recursion (WITH RECURSIVE), which subqueries cannot do at all. The optimisation story is the interview meat. Before PostgreSQL 12, every CTE was an optimisation fence: it was always materialised into a temporary result, and predicates from the outer query could not be pushed down into it, so a CTE selecting from a huge table and an outer WHERE id = 42 still scanned everything inside the CTE.

PostgreSQL 12 changed the default: CTEs referenced once and free of side effects are now inlined like subqueries, and you control it explicitly with WITH x AS MATERIALIZED (...) to force the fence or AS NOT MATERIALIZED to force inlining. CTEs referenced multiple times are still materialised by default. This history matters because folk advice ('CTEs are slow in Postgres') is version-dependent, and saying so precisely is a strong signal.

MySQL 8 introduced CTEs (there are none in 5.7) and its optimiser decides between merging and materialisation itself, with optimizer_switch flags influencing derived-table merging. One more production point: a materialised CTE has no indexes, so joining a large materialised CTE to another large set can be brutal; for genuinely large intermediates, a temporary table with an explicit index is the better tool.

-- Pipeline-style readability
WITH delivered AS (
  SELECT customer_id, amount, created_at
  FROM orders
  WHERE status = 'DELIVERED'
),
monthly AS (
  SELECT customer_id,
         DATE_TRUNC('month', created_at) AS month,
         SUM(amount) AS spend
  FROM delivered
  GROUP BY 1, 2
)
SELECT * FROM monthly WHERE spend > 50000;

-- PostgreSQL 12+: explicit control over the optimisation fence
WITH big AS NOT MATERIALIZED (
  SELECT * FROM events        -- outer WHERE can now push down
)
SELECT * FROM big WHERE user_id = 42;

Answer

A recursive CTE has two parts joined by UNION ALL inside WITH RECURSIVE: an anchor member that seeds the result (the CEO: WHERE manager_id IS NULL) and a recursive member that references the CTE itself, joining the previous iteration's rows to find the next level (employees whose manager_id is in the last batch). The engine iterates: run the anchor, then repeatedly run the recursive member against only the rows produced by the previous iteration, appending results, until an iteration returns zero rows. It is breadth-first accumulation, not function-call recursion.

Standard embellishments interviewers expect: a depth column (1 in the anchor, depth + 1 in the recursive member) to answer 'how many levels', and a path column (CONCAT of names) both for display and for cycle detection. Cycles are the classic failure: if bad data makes A manage B and B manage A, the query never terminates. Defences are engine-specific and quotable: MySQL has cte_max_recursion_depth (default 1000, error 3636 'Recursive query aborted after 1001 iterations' when hit); PostgreSQL has no depth limit, so you guard manually with WHERE depth < 20 or path-based checks, and PostgreSQL 14 added SEARCH and CYCLE clauses that generate ordering and cycle-mark columns for you.

Also say when NOT to model hierarchies this way at scale: for very deep or very hot trees, alternatives like materialised path columns, closure tables, or PostgreSQL's ltree extension trade write complexity for O(1) subtree reads. Recursive CTEs also generate series (numbers, calendar dates) in MySQL, which lacks generate_series.

WITH RECURSIVE org AS (
  -- Anchor: the root(s)
  SELECT employee_id, name, manager_id,
         1 AS depth,
         name::TEXT AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive member: next level down
  SELECT e.employee_id, e.name, e.manager_id,
         o.depth + 1,
         o.path || ' > ' || e.name
  FROM employees e
  JOIN org o ON e.manager_id = o.employee_id
  WHERE o.depth < 20          -- cycle/depth guard
)
SELECT * FROM org ORDER BY path;

-- MySQL: generate a date spine (no generate_series)
WITH RECURSIVE dates AS (
  SELECT DATE('2026-01-01') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < '2026-12-31'
)
SELECT d FROM dates;

Answer

A B-tree (technically B+tree) keeps keys sorted in a balanced tree of pages: root, internal pages, and leaf pages linked left-to-right. A lookup descends from root to leaf in O(log N) page reads, typically 3-4 for hundreds of millions of rows, then range scans walk the leaf chain. Leaves store the indexed key plus a row locator: the primary key value in InnoDB secondary indexes (so every secondary lookup does a second descent into the clustered index) or the heap tuple id in PostgreSQL.

Because keys are sorted, B-trees serve equality, ranges, prefix LIKE, ORDER BY, and MIN/MAX. The interview's core is when the index is ignored. One: functions or expressions on the column, WHERE LOWER(email) = ... or DATE(created_at) = ..., unless you build an expression index (PostgreSQL: CREATE INDEX ON users (LOWER(email)); MySQL 8.0.13+ functional indexes or an indexed generated column).

Two: implicit type casts, the classic being WHERE phone = 9876543210 against a VARCHAR phone column; MySQL casts the COLUMN to a number, disabling the index and even risking wrong matches, so always quote string literals. Three: leading wildcards in LIKE. Four: low selectivity, if 90% of rows match status = 'ACTIVE', a full scan is genuinely cheaper than millions of random heap fetches, and the optimiser is right to skip the index; partial indexes (PostgreSQL: WHERE status = 'PENDING') fix the useful minority case.

Five: stale statistics or an OR across different columns (rewrite as UNION ALL, or rely on PG's bitmap-OR). Saying 'the optimiser refusing an index is often correct' is itself a senior answer.

-- Expression index rescues a function-wrapped filter (PostgreSQL)
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'a@b.com';  -- index used

-- MySQL 8.0.13+ functional index
CREATE INDEX idx_orders_day ON orders ((CAST(created_at AS DATE)));

-- Implicit-cast trap: phone is VARCHAR
SELECT * FROM users WHERE phone = 9876543210;   -- full scan (MySQL casts column)
SELECT * FROM users WHERE phone = '9876543210'; -- index seek

-- Partial index for the rare-but-hot state (PostgreSQL)
CREATE INDEX idx_jobs_pending ON jobs (created_at)
WHERE status = 'PENDING';

Answer

A composite index on (a, b, c) sorts entries by a, then b within equal a, then c within equal (a, b), like a phone book sorted by surname then first name. The leftmost-prefix rule follows directly: the index can serve predicates on (a), (a, b), or (a, b, c), but a query filtering only on b or c cannot seek, because b values are scattered across the a groups. Equally important and less known: a range predicate stops index navigation for the columns after it.

For WHERE a = 1 AND b > 5 AND c = 9, the index seeks on a, range-scans b, and must filter c row-by-row; the c column in the index still helps as a filter but not as a seek. Hence the ordering rule of thumb: equality columns first, then the range or sort column, then anything else, and among equality columns put the most selective or most frequently queried alone first. ORDER BY can also ride the index: WHERE a = 1 ORDER BY b uses (a, b) with no sort; mixed directions need matching index directions, and descending index columns work in both PostgreSQL and MySQL 8 (MySQL before 8.0 parsed DESC but ignored it).

Common interview follow-ups: is an index on (a) redundant next to (a, b)? Yes for reads, almost always drop it. Does (a, b) help WHERE b = ? alone?

Not for seeking, though PostgreSQL can still choose a full index scan of (a, b) as a skinnier alternative to the table, and MySQL 8.0.13+ has a limited skip-scan optimisation. Verify with EXPLAIN, looking at key_len in MySQL to see how many index columns are actually used for seeking.

CREATE INDEX idx_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- Full seek: equality, equality, then range
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'DELIVERED'
  AND created_at >= '2026-01-01';

-- Seeks customer_id only; created_at filtered, not seeked,
-- because the status column is skipped
SELECT * FROM orders
WHERE customer_id = 42 AND created_at >= '2026-01-01';

-- Sort for free: WHERE equality + ORDER BY next index column
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'DELIVERED'
ORDER BY created_at DESC
LIMIT 20;

Answer

An index covers a query when every column the query needs (SELECT list, WHERE, ORDER BY, JOIN keys) exists inside the index, letting the engine answer entirely from the index without touching the table. That removes the random-I/O step that dominates cost when many rows match. In MySQL, EXPLAIN shows 'Using index' in the Extra column for a covering read (distinct from 'Using index condition', which is index condition pushdown, a different optimisation candidates mix up under pressure).

InnoDB has a structural bonus: secondary index leaves store the primary key, so an index on (customer_id) implicitly covers SELECT id, customer_id-style queries. In PostgreSQL, the equivalent is the index-only scan, but with an MVCC twist that makes for a great interview answer: PostgreSQL indexes do not store tuple visibility, so an index-only scan must confirm each page is all-visible via the visibility map; pages dirtied since the last VACUUM force heap fetches anyway. EXPLAIN (ANALYZE) shows 'Index Only Scan' with a 'Heap Fetches' count, and a high count means autovacuum is not keeping up, the fix being more aggressive autovacuum settings or a manual VACUUM.

PostgreSQL 11 added INCLUDE columns: CREATE INDEX ... (customer_id) INCLUDE (amount) stores amount in leaf pages as payload without making it part of the key, keeping the key small and enabling covering without affecting uniqueness semantics. The trade-off to state: every extra column fattens the index, slowing writes and eating buffer pool, so cover deliberately for hot queries rather than by default; a covering index for the top-20-per-customer listing query is a classic targeted win.

-- MySQL: covering index for a hot listing query
CREATE INDEX idx_orders_cover
  ON orders (customer_id, created_at, amount, status);
EXPLAIN SELECT created_at, amount, status
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC LIMIT 20;
-- Extra: 'Using index'  -> no table access

-- PostgreSQL 11+: INCLUDE keeps the key lean
CREATE INDEX idx_orders_cust_created
  ON orders (customer_id, created_at DESC)
  INCLUDE (amount, status);
EXPLAIN (ANALYZE, BUFFERS)
SELECT amount, status FROM orders
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;
-- Look for: Index Only Scan ... Heap Fetches: 0

Answer

EXPLAIN shows the optimiser's chosen plan; EXPLAIN ANALYZE actually executes the query and reports real row counts and timing per node (available in PostgreSQL forever, in MySQL since 8.0.18; remember it really runs the statement, so never EXPLAIN ANALYZE a DELETE on production without wrapping it in a transaction you roll back). In PostgreSQL, read the node tree inside-out: Seq Scan versus Index Scan versus Index Only Scan versus Bitmap Heap Scan at the leaves, then join strategies (Nested Loop for small outer sets with an indexed inner side, Hash Join for large unsorted sets, Merge Join for pre-sorted inputs). The single most valuable habit: compare estimated rows to actual rows on each node; a 1000x misestimate (rows=10 estimated, 100000 actual) is where plans go wrong, usually stale statistics or correlated predicates, fixed by ANALYZE or extended statistics.

Use EXPLAIN (ANALYZE, BUFFERS) and watch buffers read versus hit to see real I/O, and look for 'Sort Method: external merge Disk' as a work_mem shortfall. In MySQL, prefer EXPLAIN FORMAT=TREE (8.0.16+) or EXPLAIN ANALYZE for the same tree view; in classic tabular EXPLAIN the danger signs are type=ALL (full scan), rows in the millions on the driving table, and Extra saying 'Using filesort' or 'Using temporary', which are not always fatal but demand justification on hot paths. key shows the chosen index and key_len how much of a composite index is used for seeking. Practical triage order: is the row estimate sane, is the driving table the small one, is there an index seek where you expected one, and is a sort or temp table avoidable with a better index.

-- PostgreSQL: full detail, real execution, I/O counters
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, SUM(o.amount)
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY c.name;
-- Watch: estimated vs actual rows per node, Heap Fetches,
-- Sort Method: external merge  Disk: 51200kB   <- raise work_mem

-- MySQL 8: tree-style plan with real timings
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC LIMIT 20;
-- Danger signs in classic EXPLAIN: type=ALL, Extra='Using filesort'

Answer

A transaction groups statements into an all-or-nothing unit. ACID unpacked concretely rather than as dictionary definitions: Atomicity, partial work never survives, a crash mid-transfer rolls back the debit if the credit did not land, implemented via undo logs in InnoDB and MVCC plus WAL in PostgreSQL. Consistency, constraints and triggers hold at commit boundaries; the database moves between valid states.

Isolation, concurrent transactions do not trample each other, with the degree configurable per isolation level. Durability, once COMMIT returns, the data survives power loss, because the write-ahead log was fsynced first (InnoDB redo log with innodb_flush_log_at_trx_commit=1, PostgreSQL WAL with synchronous_commit=on; both knobs can trade durability for throughput and interviewers like hearing you name them). Mechanics: BEGIN (or START TRANSACTION) opens, COMMIT persists, ROLLBACK abandons.

Autocommit is on by default in both engines, every bare statement is its own transaction. SAVEPOINT name creates a marker inside a transaction; ROLLBACK TO SAVEPOINT undoes work after the marker while keeping the transaction alive, which is how frameworks implement nested 'transactions' and how batch jobs skip one bad record without losing the batch. PostgreSQL has a famous behaviour here: any error aborts the whole transaction, every later statement gets 'current transaction is aborted, commands ignored until end of transaction block', so retrying one failed insert inside a transaction REQUIRES savepoints; MySQL lets you continue after most statement errors. Also mention the operational hazard of long-running or idle-in-transaction sessions: they pin undo/old row versions, bloat storage, and block VACUUM in PostgreSQL, so connection-pool timeouts like idle_in_transaction_session_timeout are production hygiene.

BEGIN;

UPDATE accounts SET balance = balance - 5000
WHERE account_id = 1 AND balance >= 5000;

-- Insist the debit actually happened
-- (application checks affected-row count = 1)

SAVEPOINT before_credit;

UPDATE accounts SET balance = balance + 5000
WHERE account_id = 2;

-- On an error here, keep the transaction alive:
-- ROLLBACK TO SAVEPOINT before_credit;

INSERT INTO transfers (from_id, to_id, amount)
VALUES (1, 2, 5000);

COMMIT;

Answer

The standard defines four levels by which anomalies they forbid. READ UNCOMMITTED permits dirty reads (seeing uncommitted changes); no serious engine makes you use it, and PostgreSQL silently upgrades it to READ COMMITTED. READ COMMITTED forbids dirty reads but allows non-repeatable reads: the same row re-read within one transaction can change if someone commits in between.

REPEATABLE READ additionally freezes row re-reads, but the standard says phantoms (new rows appearing for a re-run range query) remain possible. SERIALIZABLE forbids phantoms too: the outcome must equal some serial execution order. The defaults are a stock question: MySQL/InnoDB defaults to REPEATABLE READ; PostgreSQL defaults to READ COMMITTED.

The implementations diverge in ways worth quoting. InnoDB's REPEATABLE READ largely prevents phantoms for plain reads via consistent snapshots, and for locking reads via gap/next-key locks, stronger than the standard requires, but it permits write skew and has a quirk where UPDATEs can see and act on newer committed rows than the snapshot. PostgreSQL's REPEATABLE READ is snapshot isolation: a transaction sees a frozen snapshot, and conflicting concurrent writes raise 'could not serialize access due to concurrent update', which your application must retry.

PostgreSQL SERIALIZABLE uses SSI (serializable snapshot isolation), which detects dangerous read-write patterns optimistically and aborts one transaction with SQLSTATE 40001 rather than pessimistically locking like many engines. Practical guidance interviewers want: READ COMMITTED plus explicit row locking (SELECT FOR UPDATE) or optimistic version columns covers most OLTP; REPEATABLE READ/SERIALIZABLE where invariants span multiple rows (ledger balancing); and every SERIALIZABLE consumer must implement retry-on-40001 loops, or it will fail in production under load.

-- Session-level change
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- session-wide (PG)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- MySQL

BEGIN ISOLATION LEVEL SERIALIZABLE;  -- PostgreSQL, per-transaction
SELECT SUM(balance) FROM accounts WHERE user_id = 7;
INSERT INTO withdrawals (user_id, amount) VALUES (7, 5000);
COMMIT;
-- Under contention expect:
-- ERROR: could not serialize access due to
-- read/write dependencies among transactions (SQLSTATE 40001)
-- -> application must catch and RETRY the whole transaction

Answer

A deadlock is a cycle of lock waits: transaction A holds a lock on row 1 and wants row 2, while B holds row 2 and wants row 1. Neither can proceed, so the engine detects the cycle and kills one participant. InnoDB runs immediate wait-for-graph detection and rolls back the transaction with the smallest undo footprint, returning error 1213 'Deadlock found when trying to get lock; try restarting transaction'; the last deadlock's full detail is visible in SHOW ENGINE INNODB STATUS.

PostgreSQL checks for cycles after a lock wait exceeds deadlock_timeout (default 1 second) and aborts one victim with SQLSTATE 40P01, logging both queries. The non-negotiable application consequence: deadlock errors are normal under concurrency, and every write path needs retry logic with backoff; treating 1213 as a fatal error is a bug. Prevention is about ordering and shrinking: acquire locks in a globally consistent order (always update the lower account_id first in a transfer; sort ids before multi-row UPDATE ...

WHERE id IN), keep transactions short (no network calls or user waits between BEGIN and COMMIT), index your foreign keys and WHERE columns so updates lock only intended rows instead of scanning and locking extras (a missing index turning row locks into effectively table-wide gap locking is a classic InnoDB incident), and in InnoDB be aware that REPEATABLE READ gap locks make insert-heavy contention worse, one documented reason high-concurrency shops often run READ COMMITTED with row-based binlog format. For hot single rows (a counter every request touches), no ordering trick saves you; redesign with sharded counter rows, atomic single-statement UPDATE ... SET n = n + 1, or move the hot counter to Redis.

-- Deadlock recipe (two sessions, opposite order):
-- Session A: BEGIN; UPDATE accounts SET balance=balance-1 WHERE id=1;
-- Session B: BEGIN; UPDATE accounts SET balance=balance-1 WHERE id=2;
-- Session A: UPDATE accounts SET balance=balance+1 WHERE id=2; -- waits
-- Session B: UPDATE accounts SET balance=balance+1 WHERE id=1; -- deadlock!
-- MySQL: ERROR 1213; PostgreSQL: SQLSTATE 40P01

-- Prevention: consistent lock order regardless of transfer direction
BEGIN;
SELECT * FROM accounts
WHERE id IN (:from_id, :to_id)
ORDER BY id
FOR UPDATE;          -- both rows locked in id order
UPDATE accounts SET balance = balance - :amt WHERE id = :from_id;
UPDATE accounts SET balance = balance + :amt WHERE id = :to_id;
COMMIT;

-- Diagnose the last deadlock in MySQL
SHOW ENGINE INNODB STATUS;

Answer

UPSERT means 'insert, or update the existing row if a uniqueness conflict occurs', and every idempotent ingestion path needs it. PostgreSQL: INSERT ... ON CONFLICT (column_or_constraint) DO UPDATE SET col = EXCLUDED.col, where EXCLUDED refers to the row you tried to insert; ON CONFLICT DO NOTHING is the ignore variant.

You must name a conflict target (columns with a unique index, or ON CONSTRAINT name), which is good discipline because it fails loudly if the expected constraint is missing. MySQL: INSERT ... ON DUPLICATE KEY UPDATE col = VALUES(col) traditionally; MySQL 8.0.19 added row aliases and 8.0.20 deprecated VALUES() in this context in favour of them, INSERT INTO t VALUES (...)

AS new ON DUPLICATE KEY UPDATE col = new.col. Gotchas that separate candidates: MySQL's version fires on ANY unique key violation, not a named one, so a table with multiple unique constraints can update a different row than you intended, a real bug class; PostgreSQL's named target prevents this. MySQL reports affected-rows as 1 for a fresh insert, 2 for an update, and 0 when the update changed nothing, which confuses ORM row-count checks.

Auto-increment burn: both engines can consume sequence values on conflicting inserts, inflating id gaps under heavy upsert load. In PostgreSQL, ON CONFLICT DO UPDATE can still deadlock under concurrency on multi-row inserts (order your rows consistently), and REPLACE INTO in MySQL is NOT an upsert, it is a DELETE plus INSERT, firing delete triggers, breaking foreign keys with CASCADE, and resetting columns you did not supply; flagging REPLACE INTO as a trap is a strong answer. PostgreSQL 15 added standard MERGE, better for conditional multi-action logic (update some rows, delete others, insert the rest), though ON CONFLICT remains the concurrency-safe workhorse for plain upserts.

-- PostgreSQL
INSERT INTO daily_metrics (day, metric, value)
VALUES ('2026-08-11', 'signups', 1)
ON CONFLICT (day, metric)
DO UPDATE SET value = daily_metrics.value + EXCLUDED.value;

-- MySQL 8.0.19+ row-alias form
INSERT INTO daily_metrics (day, metric, value)
VALUES ('2026-08-11', 'signups', 1) AS new
ON DUPLICATE KEY UPDATE value = value + new.value;

-- Ignore-if-exists
INSERT INTO seen_events (event_id) VALUES ('evt_123')
ON CONFLICT (event_id) DO NOTHING;          -- PostgreSQL
INSERT IGNORE INTO seen_events (event_id) VALUES ('evt_123'); -- MySQL
-- (INSERT IGNORE also swallows other errors; use knowingly)

Answer

Both collapse a group's values into one delimited string: MySQL's GROUP_CONCAT(col ORDER BY col SEPARATOR ', ') and PostgreSQL's STRING_AGG(col, ', ' ORDER BY col). They are the standard tool for 'one row per customer with a comma-separated list of their tags' style output feeding UIs, CSV exports, and denormalised search columns. The famous production failure is MySQL's group_concat_max_len: the result is silently TRUNCATED at 1024 bytes by default.

No error, no warning that most drivers surface, just a tag list that ends mid-word once a customer crosses the threshold, and a bug report months later. The fix is SET SESSION group_concat_max_len = 1000000 (or globally in my.cnf), sized against max_allowed_packet. Quoting that default number is the strongest possible signal on this question.

PostgreSQL's STRING_AGG has no such cap short of memory. Related tools worth naming: DISTINCT inside the aggregate (GROUP_CONCAT(DISTINCT tag)), array_agg in PostgreSQL when the consumer wants a real array instead of a string (and JSON_ARRAYAGG/JSON_OBJECTAGG in both engines for structured output that does not break when values contain your separator, the second classic bug: a value containing ', ' corrupts naive downstream splitting). The design caution to volunteer: aggregating strings in SQL is presentation logic; storing GROUP_CONCAT output back into a table reintroduces the comma-separated-values anti-pattern that 1NF exists to prevent, so keep it at the query edge. For very large groups, both functions hold the whole result row in memory, so a group with a million values belongs in a different design (pagination over the child table), not a bigger buffer.

-- MySQL
SELECT c.customer_id,
       GROUP_CONCAT(DISTINCT t.tag
                    ORDER BY t.tag SEPARATOR ', ') AS tags
FROM customers c
JOIN customer_tags t ON t.customer_id = c.customer_id
GROUP BY c.customer_id;
-- Silent truncation guard:
SET SESSION group_concat_max_len = 1000000;

-- PostgreSQL
SELECT c.customer_id,
       STRING_AGG(DISTINCT t.tag, ', ' ORDER BY t.tag) AS tags,
       ARRAY_AGG(t.tag ORDER BY t.tag)                 AS tags_array
FROM customers c
JOIN customer_tags t ON t.customer_id = c.customer_id
GROUP BY c.customer_id;

-- Separator-proof structured output (both engines)
SELECT customer_id, JSON_ARRAYAGG(tag) FROM customer_tags GROUP BY customer_id;

Answer

Sargable (search-argument-able) means the predicate's shape lets the engine seek an index: the indexed column stands alone on one side of the comparison, compared against a constant or expression the engine can evaluate first. column >= '2026-08-01' is sargable; DATE(column) = '2026-08-01', column + 0 = 5, LOWER(email) = ..., and SUBSTR(pan, 1, 3) = 'ABC' are not, because the engine would have to compute the function for every row before comparing. Rewrites, not more hardware, fix these: date filters become half-open ranges (col >= day AND col < day + 1); case-insensitive lookups get an expression index on LOWER(email) or a case-insensitive collation; prefix extraction becomes LIKE 'ABC%'; YEAR(created_at) = 2026 becomes a range over the year's boundaries. The sneakier killer is implicit type conversion, because the SQL looks clean.

MySQL comparing a VARCHAR column to a numeric literal casts the COLUMN side to a number, disabling the index and, worse, making '9876543210abc' equal 9876543210. Joining tables whose join columns differ in collation (utf8mb4_general_ci versus utf8mb4_0900_ai_ci, common after partial migrations) forces a cast that kills index use on one side, and EXPLAIN just shows a mysterious full scan. In PostgreSQL, comparing a BIGINT column to a NUMERIC-typed parameter, or letting a driver send text where the column is integer, can produce the same effect.

Diagnosis: MySQL EXPLAIN shows type=ALL with the index listed in possible_keys but key=NULL, and EXPLAIN FORMAT=JSON or the warnings after EXPLAIN show the injected CAST; PostgreSQL EXPLAIN shows a Seq Scan with the predicate wrapped in a cast around the column. The habit interviewers reward: match types and collations end-to-end, and treat any function around an indexed column in WHERE as a review-blocking smell.

-- Non-sargable -> sargable rewrites
-- 1) Date bucket
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-11';      -- scan
SELECT * FROM orders WHERE created_at >= '2026-08-11'
                       AND created_at <  '2026-08-12';           -- seek

-- 2) Year filter
SELECT * FROM orders WHERE YEAR(created_at) = 2026;              -- scan
SELECT * FROM orders WHERE created_at >= '2026-01-01'
                       AND created_at <  '2027-01-01';           -- seek

-- 3) Implicit cast (phone VARCHAR): column gets cast, index dies
SELECT * FROM users WHERE phone = 9876543210;                    -- scan
SELECT * FROM users WHERE phone = '9876543210';                  -- seek

-- 4) Function needs an expression index instead
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Answer

Cross-table updates are everyday work (backfill a denormalised column, sync a mirror table, apply a staged correction file) and the syntax is annoyingly dialect-specific, which is exactly why interviewers use it. MySQL uses multi-table UPDATE with join syntax before SET: UPDATE orders o JOIN customers c ON c.customer_id = o.customer_id SET o.customer_city = c.city WHERE ... . PostgreSQL uses UPDATE ...

SET ... FROM: UPDATE orders o SET customer_city = c.city FROM customers c WHERE c.customer_id = o.customer_id, and note you do not repeat the target table in FROM. Two correctness traps to name.

First, non-deterministic multi-matches: if the join finds several source rows per target row, PostgreSQL applies an arbitrary one (documented behaviour, no error) and MySQL similarly applies one; when the source can contain duplicates, aggregate or deduplicate it in a derived table first, or add a uniqueness guarantee. Second, unmatched rows: with UPDATE ... FROM, target rows with no join match are simply not updated, which is usually what you want, but if the intent is 'set NULL when missing' you need a LEFT-JOIN-shaped rewrite or a follow-up statement.

PostgreSQL's RETURNING clause returns the updated rows in the same round trip (UPDATE ... RETURNING order_id, customer_city), invaluable for audit logs and queue patterns; MySQL has no UPDATE ... RETURNING (MariaDB added one for DELETE/INSERT).

For big backfills, batch by primary-key ranges, keep transactions small, and on MySQL watch binlog growth with row-based replication, the same hygiene as mass deletes. Correlated-subquery SET (SET col = (SELECT ...)) is portable but per-row; the join forms are the performant tool.

-- MySQL: multi-table UPDATE
UPDATE orders o
JOIN customers c ON c.customer_id = o.customer_id
SET o.customer_city = c.city
WHERE o.customer_city IS NULL;

-- PostgreSQL: UPDATE ... FROM (+ RETURNING)
UPDATE orders o
SET customer_city = c.city
FROM customers c
WHERE c.customer_id = o.customer_id
  AND o.customer_city IS NULL
RETURNING o.order_id, o.customer_city;

-- Dedupe the source first when it may multi-match
UPDATE orders o
SET latest_note = s.note
FROM (
  SELECT DISTINCT ON (order_id) order_id, note
  FROM order_notes
  ORDER BY order_id, created_at DESC
) s
WHERE s.order_id = o.order_id;

Answer

PostgreSQL has two types: json (stored as text, preserves key order and duplicates, reparsed on every access) and jsonb (binary, deduplicated keys, indexable), and jsonb is the correct default. Operators: -> returns a json value, ->> returns text, #>> descends a path, @> tests containment (does this document contain this sub-document), and ? tests key existence. The killer feature is GIN indexing: CREATE INDEX ...

USING GIN (attrs jsonb_path_ops) accelerates @> containment queries across millions of rows. Since PostgreSQL 12, generated columns can extract hot fields into typed, B-tree-indexed columns. MySQL's JSON type (5.7+) validates and stores binary JSON; access via JSON_EXTRACT(doc, '$.path') or the -> operator, with ->> as the unquoting shorthand, plus JSON_CONTAINS, JSON_SET, JSON_TABLE (8.0) to flatten arrays into rows.

MySQL cannot index a JSON column directly; the pattern is an indexed generated column (ALTER TABLE ... ADD col INT GENERATED ALWAYS AS (doc->>'$.price') STORED, then index it), or multi-valued indexes (8.0.17+) over JSON arrays. When is JSON right: genuinely heterogeneous attributes (product specs differing per category), webhook/event payload archival, sparse optional metadata, and vendor documents you do not control.

When it is wrong, and interviewers absolutely probe this: fields you filter, join, aggregate, or constrain belong in real columns, because JSON gives you no foreign keys, no NOT NULL per field, weak statistics for the planner, and every business rule moves into application code. The failure mode is the 'schema-less creep' where amount and status end up inside a blob and every report becomes ->> spaghetti. The balanced answer: relational core, JSON at the edges, promote fields to columns the moment they acquire query traffic.

-- PostgreSQL: jsonb + GIN containment
CREATE TABLE products (
  product_id BIGINT PRIMARY KEY,
  attrs JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX idx_products_attrs
  ON products USING GIN (attrs jsonb_path_ops);

SELECT product_id
FROM products
WHERE attrs @> '{"brand": "boAt", "wireless": true}';

SELECT attrs->>'brand' AS brand,
       (attrs->>'price_inr')::NUMERIC AS price
FROM products;

-- MySQL: index a JSON field via a generated column
ALTER TABLE products
  ADD price_inr DECIMAL(10,2)
    GENERATED ALWAYS AS (attrs->>'$.price_inr') STORED,
  ADD INDEX idx_products_price (price_inr);

SELECT JSON_EXTRACT(attrs, '$.specs.battery_mah') FROM products;

Answer

Stored procedures and functions are named routines living in the database (MySQL stored procedures, PostgreSQL functions in SQL or PL/pgSQL plus true procedures with CALL and in-procedure transaction control since PostgreSQL 11). Triggers run automatically on row events (BEFORE/AFTER INSERT/UPDATE/DELETE, referencing OLD and NEW). Where they genuinely earn their keep: batch data movement that would otherwise shuttle millions of rows over the network (a nightly aggregation touching 50M rows runs orders faster next to the data), enforcing invariants that must hold regardless of which application writes (an audit-trail trigger that captures every change to a payments table cannot be bypassed by a rogue script the way application-layer logging can), and multi-tenant permission choking (grant EXECUTE on a procedure, not table access).

The drawbacks are why modern product teams minimise them, and interviewers want the honest ledger: procedural SQL sits outside your normal deployment pipeline unless you invest in migration tooling, so versioning, code review, and rollback are weaker; unit testing is awkward compared to application code; debugging tools are primitive; MySQL's procedure language is notably clunky; database CPU is the most expensive and least horizontally scalable compute you own; and triggers specifically create invisible write amplification, an UPDATE that fires a trigger that updates another table that fires another trigger is a genuine production mystery generator, and bulk loads slow down or misbehave (MySQL row triggers fire per row with no statement-level option, and TRUNCATE fires no triggers at all). The 2026-balanced position: keep business logic in application services; use database routines for data-local batch work, integrity-critical auditing, and constraint-like invariants; document every trigger loudly in the schema repo because they are the least discoverable code in any system.

-- PostgreSQL audit trigger: unbypassable change capture
CREATE TABLE payments_audit (
  audit_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payment_id BIGINT NOT NULL,
  old_status TEXT,
  new_status TEXT,
  changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  changed_by TEXT NOT NULL DEFAULT CURRENT_USER
);

CREATE OR REPLACE FUNCTION log_payment_status() RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status IS DISTINCT FROM OLD.status THEN
    INSERT INTO payments_audit (payment_id, old_status, new_status)
    VALUES (OLD.payment_id, OLD.status, NEW.status);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_payment_status
AFTER UPDATE ON payments
FOR EACH ROW EXECUTE FUNCTION log_payment_status();

Answer

They compute several GROUP BY combinations in one scan. GROUP BY GROUPING SETS ((city, category), (city), ()) returns per-city-and-category rows, per-city subtotals, and a grand total together, where a plain GROUP BY would need three queries stitched with UNION ALL, three scans of the table instead of one. ROLLUP (a, b, c) is shorthand for the hierarchy of prefixes: (a,b,c), (a,b), (a), (), matching drill-down reports like year > month > day with subtotals at each level.

CUBE (a, b) generates every subset: (a,b), (a), (b), (), for cross-tab style analysis; PostgreSQL supports CUBE and GROUPING SETS fully, while MySQL supports only WITH ROLLUP (the older syntax GROUP BY a, b WITH ROLLUP; no CUBE, no GROUPING SETS as of MySQL 8.4), a dialect gap worth stating precisely. In the output, the subtotal rows carry NULL in the rolled-up columns, which collides with genuine NULL data values; the GROUPING() function disambiguates, returning 1 when the NULL is a subtotal artifact and 0 when it is real data, and you typically wrap it in a CASE to label rows 'ALL CITIES' style. MySQL 8.0.1+ supports GROUPING() with WITH ROLLUP too.

Performance-wise the engine shares one scan and one (or few) aggregation passes, so on a 100M-row fact table replacing four UNION ALL queries with one GROUPING SETS query is a real multi-minute win, and it also guarantees the subtotals are consistent (UNION versions can disagree if data changes between the queries). This is a data-analyst and BI-engineer favourite at Indian analytics teams because it maps one-to-one onto the subtotal-bearing Excel exports that finance still asks for.

-- PostgreSQL: detail + city subtotal + grand total, one scan
SELECT
  COALESCE(city, 'ALL CITIES')       AS city,
  COALESCE(category, 'ALL')          AS category,
  SUM(amount)                        AS revenue,
  GROUPING(city)                     AS is_city_total
FROM sales
GROUP BY GROUPING SETS ((city, category), (city), ())
ORDER BY GROUPING(city), city, GROUPING(category), category;

-- Hierarchy shorthand
SELECT city, category, SUM(amount)
FROM sales
GROUP BY ROLLUP (city, category);

-- MySQL: only WITH ROLLUP exists
SELECT city, category, SUM(amount)
FROM sales
GROUP BY city, category WITH ROLLUP;

Answer

Median is just the 50th percentile, and SQL offers two families. Ordered-set aggregates: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) interpolates between the two middle values for a continuous result, while PERCENTILE_DISC(0.5) returns an actual value from the data; PostgreSQL supports both as aggregates (usable with GROUP BY for per-department medians). MySQL does not implement PERCENTILE_CONT as an aggregate even in 8.4, a much-quoted gap, so the MySQL idioms are window-function workarounds: ROW_NUMBER plus COUNT to pick the middle row(s) and average them, or CUME_DIST/PERCENT_RANK for percentile positions.

NTILE(n) is different in kind and candidates regularly misuse it: it splits the ordered partition into n buckets of near-equal ROW COUNT (sizes differ by at most one), which is exactly right for decile analysis, 'label each customer's spend decile', and wrong for 'find the value at the 90th percentile', because bucket boundaries are row-count artifacts, not interpolated values; with heavy ties, equal values can straddle two buckets. PERCENT_RANK() gives each row its relative rank in [0,1] and CUME_DIST() the cumulative fraction of rows at or below it, useful for 'top 5% earners' filters. Production notes: exact percentiles require a sort of the partition, so p99 latency over billions of events is usually approximated (t-digest/HLL-family sketches in warehouses; APPROX_PERCENTILE in several engines) or pre-aggregated into histograms; and median-of-medians across shards is not the global median, a subtle distributed-systems trap worth naming in data-engineering rounds.

-- PostgreSQL: per-department median and p90
SELECT department_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees
GROUP BY department_id;

-- Decile labels per customer by spend (both engines)
SELECT customer_id, total_spend,
       NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM customer_totals;

-- MySQL median workaround (no PERCENTILE_CONT aggregate)
SELECT AVG(salary) AS median_salary
FROM (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS cnt
  FROM employees
) t
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2));

Answer

Both prevent the lost-update problem: two sessions read a row (balance 1000), both compute a new value, and the second COMMIT silently overwrites the first (two withdrawals of 800 both succeed). Pessimistic locking blocks the race up front: SELECT ... FOR UPDATE takes an exclusive row lock at read time, so the second reader waits until the first transaction commits, then sees the updated value.

Variants matter in interviews: FOR SHARE (PG) / FOR UPDATE in share-mode forms allow concurrent readers but block writers; NOWAIT errors immediately instead of queueing ('could not obtain lock on row' in PG, error 3572 in MySQL); SKIP LOCKED skips locked rows, the foundation of SQL job queues. The rules: locks live only inside a transaction, so autocommit FOR UPDATE is a no-op ritual; lock rows in consistent order to dodge deadlocks; and never hold row locks across user think-time or external API calls. Optimistic locking assumes conflicts are rare and detects them at write time: add a version INT (or updated_at) column, read it with the row, and write with UPDATE ...

SET ..., version = version + 1 WHERE id = ? AND version = ?; zero affected rows means someone else won, and the application re-reads and retries or surfaces a conflict. This is what JPA/Hibernate @Version, Rails lock_version, and Sequelize's version option generate.

Choosing between them: optimistic for web/API flows with human latency and low contention (holding DB locks across an HTTP round trip is an outage pattern); pessimistic for short, hot, high-contention critical sections like seat allocation, inventory decrement, and wallet debits. The single-statement atomic form, UPDATE wallets SET balance = balance - 800 WHERE id = ? AND balance >= 800, checked for affected rows, beats both patterns when it fits, and saying so is a senior move.

-- Pessimistic: serialize wallet debits
BEGIN;
SELECT balance FROM wallets WHERE wallet_id = 7 FOR UPDATE;
-- application verifies balance >= 800
UPDATE wallets SET balance = balance - 800 WHERE wallet_id = 7;
INSERT INTO ledger (wallet_id, delta) VALUES (7, -800);
COMMIT;

-- Optimistic: version-guarded write, retry on 0 rows
UPDATE documents
SET body = :new_body,
    version = version + 1
WHERE doc_id = :id
  AND version = :version_read_earlier;
-- affected_rows = 0  -> conflict: re-read, merge, retry

-- Often best: single atomic conditional statement
UPDATE wallets SET balance = balance - 800
WHERE wallet_id = 7 AND balance >= 800;
-- affected_rows = 0 -> insufficient funds, no race window at all

Answer

N+1 is the pattern where code fetches N parent rows with one query, then loops issuing one child query per parent: load 50 job listings, then 50 separate SELECTs for each listing's company. Each query is fast, so nothing looks slow in isolation; the page pays 51 round trips, and at 1-2 ms per round trip within a datacenter (worse across zones) that is the difference between a 20 ms and a 300 ms endpoint. It hides inside ORM lazy loading, Sequelize, Hibernate, Django, and surfaces in APM traces as a staircase of identical queries differing only in the id, which is exactly how you diagnose it: look for repeated query shapes in slow-trace waterfalls or pg_stat_statements/performance_schema aggregated by normalised query text with suspiciously high call counts.

SQL-level fixes, in order of preference: a JOIN when child cardinality is low (listings JOIN companies, one query, one round trip); a batched IN query when you want parent and child sets separate, SELECT * FROM companies WHERE company_id IN (...50 ids...), two queries total, which is what DataLoader-style batching and ORM eager loading (includes/select_related/prefetch_related/JOIN FETCH) generate under the hood; JSON aggregation to ship nested child arrays in one row per parent, SELECT l.*, (SELECT JSON_ARRAYAGG(...) FROM applicants a WHERE a.listing_id = l.id) or a LEFT JOIN with JSON_ARRAYAGG and GROUP BY, which avoids the row-multiplication of a plain join when children are many; and PostgreSQL's LATERAL join for per-parent top-N ('each listing with its 3 latest applicants'), which a plain join cannot express efficiently. The interview framing that lands: N+1 is an interface problem between application iteration and set-oriented SQL, and the cure is always 'fetch sets, not items'.

-- The staircase (pseudocode of the anti-pattern)
-- SELECT * FROM listings ORDER BY created_at DESC LIMIT 50;
-- for each listing:
--   SELECT * FROM companies WHERE company_id = ?;   -- x50

-- Fix 1: join
SELECT l.listing_id, l.title, c.name AS company
FROM listings l
JOIN companies c ON c.company_id = l.company_id
ORDER BY l.created_at DESC LIMIT 50;

-- Fix 2: batched second query (what eager loading emits)
SELECT * FROM companies
WHERE company_id IN (101, 102, 103 /* ... the 50 ids */);

-- Fix 3 (PG): per-parent top-N via LATERAL
SELECT l.listing_id, l.title, a.applicant_id, a.applied_at
FROM listings l
LEFT JOIN LATERAL (
  SELECT applicant_id, applied_at
  FROM applications a
  WHERE a.listing_id = l.listing_id
  ORDER BY applied_at DESC
  LIMIT 3
) a ON TRUE
ORDER BY l.created_at DESC;

Answer

MVCC (multi-version concurrency control) lets readers and writers proceed without blocking each other: instead of overwriting rows in place, the engine keeps multiple versions and shows each transaction the version valid for its snapshot. The implementations differ fundamentally and comparing them precisely is a strong senior answer. PostgreSQL versions rows in the table itself: every tuple carries xmin (creating transaction id) and xmax (deleting/updating transaction id), an UPDATE writes a whole new tuple and marks the old one, and visibility is decided per-tuple against the transaction's snapshot.

Dead tuples accumulate until VACUUM reclaims them, so PostgreSQL needs autovacuum as a structural consequence of its design, not as optional housekeeping. The failure modes follow: table and index bloat when vacuum falls behind (long-running transactions or abandoned replication slots hold back the xmin horizon, pinning dead tuples), and transaction id wraparound, where an un-vacuumed table approaches the 32-bit xid limit and PostgreSQL first warns, then forces aggressive vacuums, and ultimately refuses writes to protect data; every serious PG shop monitors datfrozenxid age. HOT (heap-only tuple) updates soften the cost when no indexed column changes.

InnoDB instead updates in place and writes the old version into undo logs in the system/undo tablespaces; readers needing an older version walk the undo chain, and the purge thread trims undo history once no transaction needs it. So InnoDB has no VACUUM, but a long-running transaction still causes trouble: history list length grows, undo tablespaces balloon, and reads get slower as version chains lengthen. Practical monitoring: pg_stat_user_tables.n_dead_tup and autovacuum logs in PostgreSQL; SHOW ENGINE INNODB STATUS history list length and information_schema.innodb_metrics in MySQL. Both designs share the same operational moral: kill long-lived idle transactions.

-- PostgreSQL: bloat and vacuum visibility
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- Wraparound safety margin
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

-- Who is holding the horizon back?
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;

-- MySQL: undo pressure indicator
SHOW ENGINE INNODB STATUS;  -- read 'History list length'

Answer

Cost-based optimisers estimate, for each candidate plan, how many rows each step produces and what it costs, using per-column statistics: number of distinct values, NULL fraction, most-common values with frequencies, and histograms of the distribution. PostgreSQL collects these via ANALYZE (autovacuum triggers it) into pg_statistic, sample size controlled by default_statistics_target (default 100, raisable per column). MySQL 8 keeps persistent index statistics (innodb_stats_persistent) and added optional column histograms via ANALYZE TABLE ...

UPDATE HISTOGRAM ON col, which matter precisely for non-indexed filter columns. Plans go wrong in predictable ways. Stale stats after bulk loads: the table grew 10x overnight and the planner still thinks it is small, choosing nested loops that explode; fix with a manual ANALYZE after big loads.

Correlated columns: the planner multiplies selectivities as if independent, so WHERE city = 'Mumbai' AND pincode LIKE '400%' is estimated far too selective; PostgreSQL's CREATE STATISTICS (dependencies, ndistinct, mcv) teaches it the correlation. Skewed data: one merchant owns 40% of rows, so merchant_id = :param has wildly different correct plans per value; prepared statements that lock a generic plan amplify this (PostgreSQL's plan_cache_mode and the first-five-executions heuristic are the levers). Parameter-shaped disasters also hide in ORMs.

Diagnosis is always EXPLAIN ANALYZE and hunting the node where estimated and actual rows diverge by orders of magnitude. Escalation options differ by engine and by philosophy: MySQL embraces hints (USE INDEX/FORCE INDEX, optimizer hints like /*+ JOIN_ORDER(...) */); PostgreSQL core refuses hints, so you reach for statistics improvements, rewrites (OFFSET 0 fences, CTEs AS MATERIALIZED, LATERAL restructures), per-query enable_seqscan/enable_nestloop toggles in a session for surgery, or the pg_hint_plan extension where policy allows. The senior framing: fix the estimate, not the plan; forced plans rot silently as data drifts.

-- PostgreSQL: teach the planner about correlated columns
CREATE STATISTICS city_pincode_dep (dependencies, mcv)
  ON city, pincode FROM addresses;
ANALYZE addresses;

-- Raise sampling for a skewed hot column
ALTER TABLE orders ALTER COLUMN merchant_id SET STATISTICS 1000;
ANALYZE orders;

-- MySQL 8: histogram on a non-indexed filter column
ANALYZE TABLE orders UPDATE HISTOGRAM ON delivery_state WITH 64 BUCKETS;

-- MySQL: last-resort forced index
SELECT * FROM orders FORCE INDEX (idx_orders_created)
WHERE created_at >= '2026-08-01' ORDER BY created_at LIMIT 100;

Answer

A phantom read is when a transaction runs the same range query twice and new rows appear (or vanish) in between because another transaction committed inserts or deletes into the range. It differs from a non-repeatable read, which concerns changes to rows already read. The two flagship engines prevent phantoms with opposite philosophies.

InnoDB is pessimistic: under REPEATABLE READ, locking reads (SELECT ... FOR UPDATE/FOR SHARE, UPDATE, DELETE) take next-key locks, a record lock plus a gap lock on the interval before the record, so a SELECT ... WHERE amount BETWEEN 100 AND 200 FOR UPDATE locks not only matching rows but the gaps between index entries, blocking any INSERT into that range until commit.

Plain non-locking reads avoid phantoms differently, via the consistent snapshot taken at first read. Costs: gap locks throttle insert concurrency around hot ranges, produce deadlocks that confuse teams (two transactions can both hold insert-intention waits on the same gap), and all of it depends on a usable index, a range scan without one escalates to locking effectively the whole table. Switching to READ COMMITTED disables most gap locking, one reason large MySQL fleets run it.

PostgreSQL is optimistic: REPEATABLE READ (snapshot isolation) already never shows phantoms within a snapshot, but write skew across transactions remains possible; SERIALIZABLE adds SSI, which tracks read/write dependencies with predicate locks (SIReadLocks, visible in pg_locks) that block nobody, and aborts a transaction with SQLSTATE 40001 only when a dangerous dependency cycle would make the outcome non-serialisable. Costs: aborted transactions must be retried by the application, and memory for predicate lock tracking. Interview-grade summary: InnoDB pays with blocking and deadlocks up front; PostgreSQL pays with retries at commit; both require the application to expect and handle serialisation failures.

-- InnoDB REPEATABLE READ: gap locking demo
-- Session A
BEGIN;
SELECT * FROM bookings
WHERE seat_no BETWEEN 10 AND 20
FOR UPDATE;              -- next-key locks rows AND gaps in [10, 20]

-- Session B (blocks until A commits)
INSERT INTO bookings (seat_no, user_id) VALUES (15, 42);

-- PostgreSQL SERIALIZABLE: no blocking, but possible abort
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM bookings WHERE seat_no BETWEEN 10 AND 20;
INSERT INTO bookings (seat_no, user_id) VALUES (15, 42);
COMMIT;
-- Concurrent conflicting txn may get:
-- ERROR: could not serialize access due to read/write dependencies
-- HINT: The transaction might succeed if retried.

Answer

Partitioning splits one logical table into physical child tables by a partition key: RANGE (time buckets, the dominant use), LIST (by region or tenant class), and HASH (spreading write hotspots). PostgreSQL has had declarative partitioning since version 10 (CREATE TABLE ... PARTITION BY RANGE, then CREATE TABLE ...

PARTITION OF ... FOR VALUES FROM ... TO ...), with steady improvements to pruning and partition-wise joins in later releases; MySQL supports PARTITION BY RANGE/LIST/HASH/KEY with everything stored inside one InnoDB table space structure per partition.

The two real benefits: partition pruning, where the planner touches only partitions that can match the WHERE clause (a query on last week hits one weekly partition instead of three years of data; verify in EXPLAIN, which lists scanned partitions), and instant data lifecycle, where dropping or detaching an old partition replaces a multi-hour batched DELETE with a metadata operation, the number-one reason log, event, and audit tables get partitioned. Pruning requires the partition key in the predicate in prunable form: wrap the key in a function and you scan every partition. The sharp edges interviewers want you to know: primary keys and unique constraints must INCLUDE the partition key in both engines (MySQL: every unique key must contain all partition-key columns; PostgreSQL: same for primary/unique constraints), which breaks 'unique email plus partition by created_at' designs and forces rethinking uniqueness (often to application-level or a separate lookup table).

PostgreSQL global indexes do not exist; each partition has its own indexes, so per-partition index management is real (though indexes on the parent propagate). Too many partitions bloat planning time (thousands of partitions hurt in both engines, though modern PG handles pruning at execution time better). Foreign keys referencing partitioned tables were long unsupported in PG (parent-side support arrived in recent versions with caveats), and MySQL partitioned tables still cannot have foreign keys at all, a hard blocker many teams discover late.

-- PostgreSQL: monthly range partitioning
CREATE TABLE events (
  event_id   BIGINT GENERATED ALWAYS AS IDENTITY,
  user_id    BIGINT NOT NULL,
  payload    JSONB,
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (event_id, created_at)   -- must include partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events
  FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- Pruning: EXPLAIN shows only events_2026_08 scanned
EXPLAIN SELECT * FROM events
WHERE created_at >= '2026-08-05' AND created_at < '2026-08-06';

-- Retention becomes metadata, not DELETE
ALTER TABLE events DETACH PARTITION events_2026_08;
DROP TABLE events_2026_08;

Answer

Replication streams changes from a primary to replicas. MySQL replicates the binary log: row-based format (binlog_format=ROW, the modern default) ships changed row images, replicas apply via parallel workers, and GTIDs (gtid_mode=ON) give every transaction a global id making failover and re-pointing replicas sane. Asynchronous by default; semi-synchronous plugins make the primary wait for at least one replica's acknowledgement of receipt (not apply), and Group Replication/InnoDB Cluster provide consensus-based setups.

PostgreSQL ships WAL: streaming physical replication to standbys, synchronous_commit plus synchronous_standby_names for durability guarantees, replication slots so the primary retains WAL for a disconnected replica (with the classic footgun that an abandoned slot fills the disk), and logical replication (publications/subscriptions) for selective table streaming, version-crossing upgrades, and CDC via the logical decoding interface that Debezium uses. The application-facing problem is lag: a user updates their profile, the next page's read hits a replica that has not applied the change, and support gets 'my edit vanished' tickets. Monitoring: SHOW REPLICA STATUS's Seconds_Behind_Source (imperfect, measures apply of the currently-executing event), or better, a heartbeat table; PostgreSQL exposes pg_stat_replication LSN positions on the primary and pg_last_wal_replay_lsn on standbys, and replays can pause under standby query conflicts (hot_standby_feedback trades that against primary bloat).

Read-your-writes strategies, in practical order: route the writing user's subsequent reads to the primary for a short window (session stickiness, the common pragmatic fix), track the write's GTID or LSN and have replicas wait for it (MySQL WAIT_FOR_EXECUTED_GTID_SET, PostgreSQL comparing replay LSN), or accept staleness where the product allows. Also name the operational rule: never scale writes with read replicas; they scale reads only, and every replica adds lag surface.

-- MySQL: replica health
SHOW REPLICA STATUS\G
-- Watch: Replica_IO_Running, Replica_SQL_Running,
--        Seconds_Behind_Source, Retrieved/Executed_Gtid_Set

-- Read-your-writes with GTIDs (run on the replica)
SELECT WAIT_FOR_EXECUTED_GTID_SET(':gtid_of_users_write', 1);

-- PostgreSQL: lag in bytes per standby (run on primary)
SELECT application_name,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       sync_state
FROM pg_stat_replication;

-- Forgotten slot = disk-filling time bomb
SELECT slot_name, active,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
         AS retained_wal
FROM pg_replication_slots;

Answer

Sharding splits data horizontally across independent database instances, each owning a subset of rows. It is the last resort after the cheaper ladder is exhausted: query and index tuning, caching, read replicas, connection pooling, vertical scaling (a modern 64-core RDS/Aurora or Cloud SQL instance carries most Indian-unicorn-scale OLTP), archival and partitioning to shrink the hot set. You shard when the WRITE volume or working set genuinely exceeds one primary, because sharding permanently costs you cross-shard joins, cross-shard transactions, and global unique constraints; every 'orders by merchant across all customers' query becomes scatter-gather in application code or a routing layer (Vitess for MySQL, the technology behind YouTube's and PlanetScale's scaling, and Citus for PostgreSQL, which distributes tables transparently, are the named tools worth citing).

Shard-key choice decides whether the system works. The key should appear in nearly every query (else you scatter-gather everything), distribute load evenly (avoid celebrity hotspots: sharding a social graph by user_id puts a viral creator's entire fan-out on one shard), and keep transactional units co-located (an e-commerce app sharding by customer_id keeps a customer's cart, orders, and payments on one shard, so checkout stays a local transaction; sharding by order_id would scatter one customer across shards). Hash-based keys spread evenly but lose range queries; range-based keys keep ranges but risk hot tails (time-ordered keys concentrate all inserts on the newest shard).

Resharding is the buried cost: growing from 4 to 6 shards moves data while live, so designs use many logical shards (say 4096 virtual buckets) mapped onto few physical nodes, moving buckets not rows-by-key. Also mention ID generation: auto-increment breaks across shards, so Snowflake-style ids, UUIDv7, or per-shard ranges with an allocator take over. The best interview close: sharding is an organisational commitment, not a feature flag; delay it as long as honest capacity math allows.

Answer

A materialized view stores the query's RESULT on disk, unlike a regular view which stores only the query. Reading it is as fast as reading a table (it can have its own indexes), at the price of staleness: the data is as of the last refresh. PostgreSQL supports them natively: CREATE MATERIALIZED VIEW mv AS SELECT ..., refreshed with REFRESH MATERIALIZED VIEW, which by default rewrites the whole thing under an exclusive lock that blocks readers; the production-grade variant is REFRESH MATERIALIZED VIEW CONCURRENTLY, which diffs against the current contents and swaps without blocking reads, but it requires a UNIQUE index on the matview and costs more than a plain refresh.

Scheduling lives outside the engine (pg_cron, application schedulers, or Airflow-style DAGs after upstream loads). Vanilla PostgreSQL has no incremental refresh; recomputing only changed slices is DIY (a summary TABLE maintained by triggers or by merge jobs keyed on updated_at watermarks) or an extension/warehouse feature, and knowing that limitation is exactly what gets probed. MySQL has no materialized views at all; the standard MySQL answer is a summary table maintained by scheduled jobs or triggers, stated plainly.

When they beat alternatives: versus a regular view, when the underlying aggregation is too heavy to run per request (a dashboard scanning 200M ledger rows becomes a 30 ms indexed read refreshed every 10 minutes); versus a Redis cache, when you want SQL queryability over the cached result (joins, filters, ORDER BY on the aggregate), transactional consistency with the source at refresh time, no cache-stampede logic, and one fewer system in the pipeline. The design questions to ask out loud: what staleness can the product tolerate, is refresh cost amortised well against read traffic, and does anything join against the matview (then it must be CONCURRENTLY refreshed to avoid blocking). A matview refreshed on a cadence the business signed off on is one of the highest-leverage, lowest-tech scaling tools in the relational toolbox.

-- PostgreSQL: heavy aggregate, read cheap, refreshed on schedule
CREATE MATERIALIZED VIEW merchant_daily_gmv AS
SELECT merchant_id,
       created_at::DATE AS day,
       SUM(amount)      AS gmv,
       COUNT(*)         AS orders
FROM payments
WHERE status = 'CAPTURED'
GROUP BY merchant_id, created_at::DATE;

-- Required for CONCURRENTLY refresh
CREATE UNIQUE INDEX uq_mdg ON merchant_daily_gmv (merchant_id, day);

-- Non-blocking refresh (readers keep working)
REFRESH MATERIALIZED VIEW CONCURRENTLY merchant_daily_gmv;

-- Schedule with pg_cron
SELECT cron.schedule('refresh-gmv', '*/10 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY merchant_daily_gmv');

Answer

Gaps-and-islands is the family of problems about grouping consecutive runs (islands) separated by breaks (gaps) in ordered data: login streaks, continuous sensor uptime, consecutive absent days, unbroken subscription months. It appears constantly in analytics rounds at product companies, and there is a canonical trick worth having cold: the difference between two counters is constant within a consecutive run. Number each user's distinct login dates with ROW_NUMBER() ordered by date; then date minus row_number (as an interval or by converting the date to an epoch day) yields the same anchor value for every date in a consecutive run, because both the date and the row number advance by exactly one per step.

Group by that anchor and each group is one island; COUNT(*) is the streak length, MIN and MAX are its endpoints, and a MAX over the counts per user answers 'longest streak'. Care points that separate correct answers from lucky ones: deduplicate to one row per user per day first (multiple logins in a day break the arithmetic; use DISTINCT or GROUP BY on the date), user partitioning must appear in the ROW_NUMBER (PARTITION BY user_id), and date arithmetic must be real date math, not string subtraction (in MySQL use DATE_SUB(login_date, INTERVAL rn DAY); in PostgreSQL, login_date - rn * INTERVAL '1 day' or subtract integers on a DATE directly). The alternative modern formulation uses LAG to flag run-starts (1 when the previous date is not yesterday) and a running SUM over the flags as the island id, which generalises better to tolerance windows ('streak survives a 1-day gap') and to non-date sequences. Sketching both, then coding one cleanly, is a maximal answer.

-- Longest daily-login streak per user (PostgreSQL)
WITH days AS (
  SELECT DISTINCT user_id, login_at::DATE AS d
  FROM logins
),
anchored AS (
  SELECT user_id, d,
         d - (ROW_NUMBER() OVER (
               PARTITION BY user_id ORDER BY d
             ))::INT AS anchor
  FROM days
),
islands AS (
  SELECT user_id, anchor,
         COUNT(*) AS streak_len,
         MIN(d)   AS streak_start,
         MAX(d)   AS streak_end
  FROM anchored
  GROUP BY user_id, anchor
)
SELECT user_id, streak_len, streak_start, streak_end
FROM (
  SELECT i.*, ROW_NUMBER() OVER (
    PARTITION BY user_id ORDER BY streak_len DESC, streak_start
  ) AS rn
  FROM islands i
) best
WHERE rn = 1;

Answer

The naive SQL queue, workers SELECT the oldest pending job then UPDATE it to running, collapses under concurrency: either every worker grabs the same job (double processing) or FOR UPDATE serialises all workers behind the first lock (a convoy where throughput equals one worker). SKIP LOCKED, available in PostgreSQL since 9.5 and MySQL since 8.0, fixes both: SELECT ... FOR UPDATE SKIP LOCKED makes each worker skip rows other transactions hold locked and claim the first free one, giving N workers N different jobs with zero coordination.

The canonical PostgreSQL claim is a single statement: UPDATE jobs SET status='running', locked_by=:worker WHERE id = (SELECT id FROM jobs WHERE status='pending' ORDER BY priority DESC, id LIMIT 1 FOR UPDATE SKIP LOCKED) RETURNING *, atomic claim and fetch in one round trip. Correctness details that make or break the design: the claim and the processing must not share one transaction if jobs run long (holding a transaction open for a 5-minute job pins MVCC horizons; instead commit the claim, then process, then mark done), which reintroduces the crashed-worker problem, solved with a lease: a locked_until timestamp set at claim time plus a reaper query that returns expired running jobs to pending, making processing effectively at-least-once, so handlers must be idempotent (idempotency keys on side effects). Add an attempts counter with a max to park poison messages in a dead-letter state instead of retry-looping forever.

An index on (status, priority DESC, id) keeps the claim query from scanning; deleting or archiving completed rows keeps the hot set small (a jobs table that only grows becomes its own incident). Compared with real brokers: SQS/RabbitMQ/Kafka win at high throughput and fan-out, but the SQL queue wins transactional enqueue (job insert commits atomically with the business write, eliminating the dual-write problem) and needs no new infrastructure, which is why pgboss, Solid Queue, Oban, and Sidekiq-style SQL backends standardise exactly this pattern.

-- Schema
CREATE TABLE jobs (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  job_type     TEXT NOT NULL,
  payload      JSONB NOT NULL,
  status       TEXT NOT NULL DEFAULT 'pending',
  priority     INT  NOT NULL DEFAULT 0,
  attempts     INT  NOT NULL DEFAULT 0,
  locked_until TIMESTAMPTZ
);
CREATE INDEX idx_jobs_claim ON jobs (status, priority DESC, id);

-- Atomic claim (PostgreSQL)
UPDATE jobs
SET status = 'running',
    attempts = attempts + 1,
    locked_until = NOW() + INTERVAL '5 minutes'
WHERE id = (
  SELECT id FROM jobs
  WHERE status = 'pending'
  ORDER BY priority DESC, id
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, job_type, payload;

-- Reaper: reclaim crashed workers' jobs
UPDATE jobs SET status = 'pending'
WHERE status = 'running' AND locked_until < NOW();

Answer

The danger is twofold: some DDL rewrites the whole table (hours on a 500M-row table), and even fast DDL needs a metadata lock that can queue behind one long-running query and then block every subsequent query, the metadata-lock pileup that has taken down plenty of production systems. Engine specifics first. MySQL 8 online DDL supports ALGORITHM=INSTANT for a growing set of changes (notably ADD COLUMN since 8.0.12, subject to conditions), INPLACE for many others (with LOCK=NONE allowing concurrent DML), and COPY as the worst case; always state the expectation explicitly, ALTER TABLE ...

ALGORITHM=INSTANT, so the statement FAILS if the engine would silently pick a heavier algorithm, rather than surprising you. Even INSTANT needs the metadata lock briefly, so set a small lock_wait_timeout for the migration session and retry, ensuring you queue politely instead of gridlocking. For changes MySQL cannot do online (many type changes, some index operations pre-8.0), the external tools do shadow-table migration: gh-ost (GitHub's binlog-based tool, no triggers, throttleable, testable on replicas) and Percona's pt-online-schema-change (trigger-based) copy rows to a new-shape table while streaming live changes, then cut over with a rename.

PostgreSQL: ADD COLUMN with a constant DEFAULT is metadata-only since PostgreSQL 11 (pre-11 it rewrote the table, a version boundary worth citing); CREATE INDEX CONCURRENTLY builds indexes without blocking writes (it can leave an INVALID index on failure, drop and retry); adding constraints uses the two-step NOT VALID then VALIDATE CONSTRAINT pattern so validation scans without long locks; and every migration session sets lock_timeout (say 2s) plus retries, converting pileups into cheap retries. Universal choreography for application compatibility: expand-migrate-contract. Add the new column/shape (backward compatible), dual-write and backfill in batches, switch reads, then drop the old shape in a later release. Never rename a column in place on a hot table; add, migrate, drop.

-- MySQL 8: demand the cheap algorithm or fail loudly
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders
  ADD COLUMN payment_source VARCHAR(32) NULL,
  ALGORITHM=INSTANT;

-- PostgreSQL migration-session hygiene
SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN payment_source TEXT;  -- metadata-only

-- Index without blocking writes (cannot run inside a transaction)
CREATE INDEX CONCURRENTLY idx_orders_payment_source
  ON orders (payment_source);
-- On failure: DROP INDEX CONCURRENTLY (it is left INVALID), retry

-- Constraint on a big table without a long lock
ALTER TABLE orders ADD CONSTRAINT chk_source
  CHECK (payment_source IN ('upi','card','netbanking','wallet'))
  NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT chk_source;

Answer

Interviewers want a repeatable process, not a lucky guess. Step one: identify the query, from APM traces or the database's own accounting. PostgreSQL: pg_stat_statements (the extension every production PG should preload) ranks normalised queries by total_exec_time, mean_exec_time, and calls; a query whose mean jumped or whose calls exploded (an app-deploy-induced N+1) is your suspect.

MySQL: the slow query log (long_query_time tuned to something honest like 100ms, log_queries_not_using_indexes judiciously) digested with pt-query-digest, or performance_schema's events_statements_summary_by_digest for the same ranking, and the sys schema views (sys.statements_with_full_table_scans) for shortcuts. Step two: separate 'the query got slower' from 'the system got slower'. Check saturation (CPU, IOPS burst balance on cloud volumes, connection counts versus max_connections, a pool exhausted by one slow query causes queueing that looks like everything is slow), locks (pg_locks joined to pg_stat_activity, or performance_schema.data_lock_waits and SHOW ENGINE INNODB STATUS for lock waits and recent deadlocks), and replication health if reads hit replicas.

A wait-event snapshot (pg_stat_activity.wait_event, or Performance Schema waits) tells you whether time goes to I/O, locks, or CPU. Step three: EXPLAIN ANALYZE the suspect with production-shaped parameters, comparing estimated versus actual rows per node; overnight plan flips have famous causes worth listing: statistics refreshed at a bad moment or gone stale after a bulk load (fix: ANALYZE, extended statistics), data crossing an optimiser threshold (the index that was winning is now judged too expensive), a parameter value hitting a skewed segment (generic plan trap), or an index dropped/bloated. Step four: shortest safe fix first, in order: ANALYZE the table, add or fix the index (CONCURRENTLY), rewrite the query, then structural work (partitioning, matviews, caching).

Step five: close the loop, alerting on p95 statement latency and lock waits so the next flip pages you before users notice. Naming that last step is what makes the answer senior.

-- PostgreSQL: who is eating the database?
SELECT LEFT(query, 60)   AS q,
       calls,
       ROUND(mean_exec_time::NUMERIC, 1) AS mean_ms,
       ROUND(total_exec_time::NUMERIC / 1000, 1) AS total_s,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

-- What is everyone waiting on right now?
SELECT state, wait_event_type, wait_event, COUNT(*)
FROM pg_stat_activity
GROUP BY 1, 2, 3 ORDER BY 4 DESC;

-- Who blocks whom?
SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY (pg_blocking_pids(blocked.pid));

-- MySQL: digest ranking without the slow log
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1e12, 1) AS total_s
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

Answer

Interviewers increasingly filter for people whose SQL knowledge did not freeze in 2015, and a handful of concrete deltas signal currency. MySQL 8.0 (2018 onward, now continued by the 8.4 LTS line and numbered innovation releases) was a genuine break: window functions, CTEs and WITH RECURSIVE, EXPLAIN ANALYZE (8.0.18), invisible indexes for safe drop-testing (mark an index invisible, watch the workload, then drop with confidence), descending indexes that actually work, instant ADD COLUMN, atomic DDL, roles for permission bundles, INTERSECT/EXCEPT (8.0.31), and JSON_TABLE to flatten JSON into rows; utf8mb4 became the default charset. Anyone claiming MySQL and unable to use a window function is dated.

PostgreSQL's recent majors added declarative partitioning maturity, parallel query, SQL/JSON standard functions, MERGE (PG 15), UNIQUE NULLS NOT DISTINCT (15), the SEARCH/CYCLE clauses for recursive CTEs (14), multiranges (14), and steady performance work on sorting, vacuum, and logical replication (row filtering and column lists in 15+); recent versions also added SQL/JSON constructors and JSON_TABLE, closing the gap with MySQL's JSON tooling. Around the engines, the ecosystem shifted in ways worth naming in interviews: serverless and branching database platforms (Aurora Serverless v2, Neon for PG, PlanetScale's Vitess-based MySQL with schema-branching workflows) changed how teams do migrations and preview environments; pgvector made PostgreSQL a default store for embedding search in AI features, which Indian product teams adopted widely in the RAG wave; DuckDB became the standard in-process analytics engine for local and pipeline analytics; and HTAP/columnar options (TiDB, ClickHouse alongside OLTP stores) formalised the OLTP-versus-OLAP split. The honest framing to close with: the SQL language moved (window functions, standard JSON, MERGE are table stakes), but the fundamentals interviewers weight most, indexing, transactions, MVCC, join strategies, remain exactly as decisive as a decade ago.