Databases 05 — SQL & Database Interview Practice

An INNER JOIN returns only rows that match on both sides — friends who have at least one expense. A LEFT JOIN returns every row from the left table, plus matches from the right, with NULLs where there is no match — all friends, including those with zero expenses. A CROSS JOIN returns every combination of left and right rows (the Cartesian product), which you almost never want by accident.

The classic surprise: putting a condition on the right table in the WHERE clause instead of the ON clause silently turns a LEFT JOIN back into an INNER JOIN. LEFT JOIN expense e ON e.paid_by = f.id WHERE e.amount_paise > 100 drops every friend with no expenses, because their NULL amount fails the WHERE. To keep them, the condition belongs in the ON. Covered in SQL joins.

The “including friends who paid nothing” is the trap — it forces a LEFT JOIN from friend, and COALESCE to turn the NULL sum into 0.

SELECT f.name,
       COALESCE(SUM(e.amount_paise), 0) AS total_paid_paise
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
GROUP BY f.id, f.name
ORDER BY total_paid_paise DESC;

Key points an interviewer is checking: LEFT JOIN (not INNER, or you lose the zero-spenders), GROUP BY f.id (group by the primary key, then you can safely select f.name), COALESCE so the answer is 0 not NULL, and ordering by the aggregate alias. See aggregation and joins.

WHERE filters individual rows before grouping; HAVING filters groups after aggregation. You cannot put an aggregate in WHERE (WHERE SUM(...) > 1000 is an error) — that is what HAVING is for. Conversely, filtering on a plain column belongs in WHERE, not HAVING, because WHERE runs first and on fewer rows.

The index point: WHERE can use an index to avoid reading rows at all, because it runs before the grouping. HAVING operates on already-aggregated groups, so an index on the raw column does not help it directly. The performance rule: filter as much as possible in WHERE so fewer rows ever reach the GROUP BY. See aggregation.

A window function computes across a set of rows related to the current row (the “window”) without collapsing them into one row — unlike GROUP BY, which returns one row per group. You get the aggregate and keep every original row.

Where it wins: “show each expense alongside its group’s running total” or “rank each friend’s expenses largest-first within their own group.” GROUP BY cannot do this because it would collapse the detail rows.

SELECT description, amount_paise, group_id,
       SUM(amount_paise) OVER (PARTITION BY group_id ORDER BY created_at) AS running_total,
       ROW_NUMBER()      OVER (PARTITION BY group_id ORDER BY amount_paise DESC) AS rank_in_group
FROM expense;

PARTITION BY is “the GROUP BY of the window,” ORDER BY inside OVER defines running order. The headline: window functions keep the rows, GROUP BY collapses them. See aggregation.

The “top-1 per group” problem — the canonical window-function or correlated-subquery interview question. Cleanest with a window function and a subquery wrapper:

SELECT group_id, description, amount_paise, paid_by
FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY amount_paise DESC) AS rn
  FROM expense e
) ranked
WHERE rn = 1;

You rank expenses within each group by amount, then keep only rank 1. The reason you cannot do it with a plain GROUP BY ... MAX(amount_paise) is that MAX gives you the amount but not the other columns of that row (description, paid_by) — selecting them would be ambiguous. The window-function-then-filter pattern is the standard answer; mention the DISTINCT ON shorthand as a PostgreSQL-specific alternative for bonus points. See aggregation.

1. Low selectivity. If the condition matches a large fraction of the table (say paid_by = 3 matching 25% of rows), each index hit pays a random jump back to the heap; for that many rows, reading the whole table sequentially is genuinely cheaper, and the planner correctly ignores the index.
2. A function or cast wrapped around the column. WHERE lower(description) = 'dinner' cannot use an index on description, because the tree stores description, not lower(description). Same for WHERE created_at::date = '2026-06-12' or WHERE amount_paise / 100 = 50. The fix is to keep the column naked on the left, or build an expression index.

A third worth mentioning: stale statistics — run ANALYZE. Always confirm with EXPLAIN ANALYZE rather than guessing. The whole topic is SQL indexes & query plans.

• group_id alone — yes. It is the leftmost column, so its values are contiguous in the sorted index (the “leftmost-prefix” rule).
• both — yes, fully. The index walks to the group, then walks one contiguous range of timestamps within it.
• created_at alone — no. Because the index sorts by group_id first, the timestamps are interleaved across all groups; there is no single sorted run of created_at to walk. It needs its own index.

The mental model: a phone book sorted by surname then first name. You can find all “Sharma,” and “Sharma, Priya,” fast — but finding every “Priya” regardless of surname means scanning the whole book. Column-order corollary: equality columns first, range columns last. From SQL indexes.

The N+1 problem: you run 1 query to fetch a list of N parents, then — often without realizing — 1 more query per parent to fetch its children, for N+1 total queries. In SplitEase: load 50 groups (1 query), then for each group lazily load its expenses (50 queries) = 51 round trips, each with network latency. It is silent in dev with 5 rows and lethal in production with thousands.

It bites hardest with ORMs: a @ManyToOne or @OneToMany that loads lazily inside a loop fires a query per iteration. Fixes: (1) a single JOIN that fetches parents and children together; (2) in JPA, a JOIN FETCH query or an @EntityGraph; (3) batching the child lookups into one IN (...) query. The tell in logs is the same query shape repeating dozens of times with different ids. See Spring Boot data & JPA and joins.

“Members who never paid” — the anti-join / NOT EXISTS pattern, a favorite because the naive NOT IN answer has a NULL trap.

SELECT f.name, gm.group_id
FROM group_member gm
JOIN friend f ON f.id = gm.friend_id
WHERE NOT EXISTS (
  SELECT 1 FROM expense e
  WHERE e.group_id = gm.group_id
    AND e.paid_by  = gm.friend_id
);

Why NOT EXISTS over NOT IN: if the subquery’s column contains any NULL, NOT IN returns no rows at all (a silent, surprising bug), whereas NOT EXISTS handles NULLs correctly and the planner usually optimizes it as an anti-join. State that trade — it is exactly the kind of detail that signals you have been burned by it before. See joins.

GROUP BY collapses rows that share the same values in the grouping columns into a single output row, and aggregates (SUM, COUNT, AVG) compute one value per group. Every selected column must either be in the GROUP BY or be inside an aggregate, because for any column not grouped, the group contains multiple different values and the database cannot pick one — it would be ambiguous. (PostgreSQL relaxes this when you group by a primary key, since the key functionally determines every other column of that table, which is why GROUP BY f.id lets you select f.name.) From aggregation.

• Atomicity — a transaction is all-or-nothing. Recording an expense and inserting its per-member shares either all commit or all roll back; you never get an expense with half its shares.
• Consistency — a transaction moves the database from one valid state to another, respecting all constraints (foreign keys, checks). You cannot commit a share pointing at a non-existent friend.
• Isolation — concurrent transactions do not step on each other; each behaves as if it ran alone (to a degree set by the isolation level). Two people settling up at once do not corrupt the balance.
• Durability — once committed, it survives a crash or power loss, because the write was flushed to durable storage (the write-ahead log) before commit was acknowledged.

ACID is the contract that lets you trust a relational database with money. See SQL transactions.

From weakest to strongest, and the anomaly each one stops:

The anomalies: a dirty read sees another transaction’s uncommitted change; a non-repeatable read gets a different value for the same row read twice; a phantom read gets new rows appearing in a repeated range query. Key real-world note: PostgreSQL’s default is Read Committed, and its Repeatable Read actually prevents phantoms too (it uses snapshot isolation, stronger than the SQL standard requires). Higher isolation means more correctness but more locking/aborts and less concurrency — that is the trade. See SQL transactions.

Normalization is organizing tables to eliminate redundant data — each fact stored in exactly one place — usually to third normal form (3NF). The payoff: no update anomalies (change a friend’s name in one row, not fifty), smaller storage, integrity enforced by structure. SplitEase is normalized: a friend’s name lives only in friend; expense references it by id.

You denormalize — deliberately duplicate data — when read performance demands it: you accept redundancy (and the burden of keeping copies in sync) to avoid expensive joins on a hot read path. Example: storing a precomputed group_balance rather than summing every expense on each page load. The rule: normalize by default for correctness, denormalize surgically with evidence (a measured slow query), never preemptively. Denormalization trades write complexity and consistency risk for read speed. See SQL data modeling and caching & consistency.

An index speeds up reads that filter, join, or sort on its column — turning a full scan into a logarithmic tree walk. But it is a second sorted structure that must stay correct forever, so it costs:

• Write amplification — every INSERT/UPDATE/DELETE must also update every index on the table (finding leaves, splitting pages). N indexes mean one logical write becomes 1 + N physical writes; write throughput drops.
• Disk and memory — indexes routinely add 50–100% to table size and compete for the buffer cache.
• Planner work and the risk of unused indexes — paying all the above for zero read benefit if nothing queries that column.

So you index the columns your queries actually use, prove each with EXPLAIN ANALYZE, and drop dead ones. SplitEase is write-heavy at month-end settlement, so over-indexing quietly halves write throughput exactly when it is busiest. Being able to argue against an index is the seniority signal. See SQL indexes.

Replication keeps copies of the database on multiple servers: a primary takes all writes and streams its changes to one or more replicas, which serve reads. This scales reads (spread them across replicas) and gives high availability (promote a replica if the primary dies).

Replication lag is the delay between a write landing on the primary and reaching a replica — usually milliseconds, but real. The trap is read-your-own-writes: a user adds an expense (write → primary), the page immediately reloads the list (read → a replica that has not caught up yet), and their new expense is missing — they think it failed and add it twice. The fix is to route a user’s reads to the primary for a short window right after they write, or use synchronous replication for that path (slower writes). Async replication = eventual consistency; you design around the lag, you do not pretend it is zero. See replication & sharding.

Replication copies the whole dataset to multiple machines (for read scaling and availability). Sharding splits one dataset across multiple machines, each holding a different slice (for write scaling and to exceed one machine’s storage). Replication is “same data, many copies”; sharding is “different data, many homes.” They are often used together — each shard is itself replicated.

The shard key (partition key) decides which shard a row lives on, and choosing it is everything:

• A bad key creates hot shards (e.g. sharding by date sends all of today’s writes to one shard) or forces cross-shard queries (sharding SplitEase by friend_id when most queries are by group means every group query fans out to many shards).
• A good key spreads load evenly and keeps the common query on a single shard.

Once chosen, the key is painful to change — it dictates data placement. Cross-shard joins and transactions are hard, which is why you shard late, only when a single (replicated) machine genuinely cannot keep up. See replication & sharding.

Decide from the data shape and access pattern, not from fashion:

Reach for relational (SQL) when data is structured with clear relationships, you need multi-row transactions and strong consistency (anything touching money), and you query it in varied ways with joins. This is the correct default — and PostgreSQL with a JSONB column covers many “I need flexible fields” cases without leaving SQL.

Reach for NoSQL when you have a genuine reason: massive horizontal write scale beyond one machine (wide-column like Cassandra), a flexible/evolving document shape queried by key (document like MongoDB), simple key-value at huge speed (Redis, DynamoDB), or relationship-heavy traversal (a graph database).

The trap to name out loud: reaching for NoSQL by default “because it scales,” then discovering you need transactions and joins after all and rebuilding them badly in application code. Start relational; move specific workloads to NoSQL when you can articulate the exact limit you are hitting. See relational vs NoSQL.

CAP says that when a distributed system hits a network partition (P — some nodes can’t talk to each other), it must choose between Consistency (C — every read sees the latest write, or errors) and Availability (A — every request gets a non-error response, possibly stale). You cannot have both during a partition.

Why “CA” is a non-choice: in any real distributed system, network partitions will happen — you do not get to opt out of P. So the real decision is CP or AP when a partition occurs. A CP system (e.g. a single-primary SQL setup) refuses writes on the minority side to stay consistent — it sacrifices availability. An AP system (e.g. Cassandra with eventual consistency) keeps serving on both sides and reconciles later — it sacrifices consistency temporarily. The concrete framing: “when the network splits, do I return a possibly-stale answer (AP) or an error (CP)?” For money you usually pick CP; for a like-counter, AP. See replication & sharding and, if live, system design scaling.

A deadlock is two (or more) transactions each holding a lock the other needs, so neither can proceed — a circular wait. Classic SplitEase trigger: transaction A locks expense row 1 then tries to lock row 2; transaction B locked row 2 first and now wants row 1. Each waits forever for the other.

The database detects the cycle and breaks it by aborting one transaction (the “victim”), which rolls back and returns a deadlock error; the application should catch it and retry. Prevention: acquire locks in a consistent order everywhere (always lock rows in id order), keep transactions short, and lower the isolation level if the workload allows. You do not eliminate deadlocks entirely — you make them rare and make your code retry-safe. See SQL transactions.

The write-ahead log is the durability mechanism: before a database changes the actual data pages, it first writes a record of the change to an append-only log on disk and flushes that to durable storage. Only then is the transaction acknowledged as committed. The rule in the name — write ahead of touching the data.

The guarantee is the D in ACID, durability: if the server crashes the instant after commit, the data pages in memory may be lost, but the WAL on disk is intact, so on restart the database replays the log and reconstructs every committed change. As a bonus, the WAL is what replication streams to replicas, and what point-in-time recovery replays. The mental model: the database promises durability by writing its intentions down before acting on them. See SQL transactions.

1. Put the right-table condition in the ON clause, not WHERE — a WHERE condition on the right table’s columns drops the unmatched left rows (whose values are NULL) and silently makes it an inner join.
2. A window function computes across related rows without collapsing them (you keep every row); GROUP BY collapses rows that share the grouping columns into one row per group.
3. Low selectivity (the query matches too much of the table, so a scan is cheaper) and a function/cast wrapped around the column (the tree indexed the raw value, not the computed one). Bonus: stale statistics.
4. One query for N parents then one query per parent for its children = N+1 round trips; fix with a single JOIN, a JPA JOIN FETCH/@EntityGraph, or batching into one IN (...).
5. Atomicity, Consistency, Isolation, Durability.
6. Read Committed; it allows non-repeatable reads and phantom reads (but prevents dirty reads).
7. Replication = same data copied to many machines (read scaling, availability); sharding = one dataset split across many machines (write/storage scaling).
8. Consistency vs Availability during a network partition — return a possibly-stale answer (AP) or an error/refuse (CP). You cannot avoid the partition (P) in a real distributed system.