SQL · Intermediate interview

A non-correlated subquery runs once, independently, and its result feeds the outer query. A correlated subquery references columns from the outer query, so conceptually it runs per outer row, which can be slow. Correlated subqueries are powerful for row-by-row comparisons but often rewrite better as joins or window functions for performance.

A CTE (WITH clause) is a named, temporary result set you reference in the main query, improving readability over nested subqueries. A recursive CTE references itself to walk hierarchies or generate sequences — an anchor query plus a recursive part that builds on the previous step until it stops. It's how you query org charts, category trees, or graph paths in standard SQL.

WITH recent AS (
  SELECT * FROM orders WHERE created > now() - interval '7 days'
)
SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;

IN checks membership in a list or subquery result; EXISTS checks whether a (usually correlated) subquery returns any row, short-circuiting on the first match. EXISTS is often faster for large or correlated checks and handles NULLs more predictably — NOT IN with a NULL in the list returns no rows, a classic gotcha, whereas NOT EXISTS behaves as expected.

Window functions compute a value across a set of rows related to the current row (the 'window') without collapsing them — so you keep every row and add an aggregate or ranking alongside it. GROUP BY collapses rows into one per group. You define the window with OVER(PARTITION BY ... ORDER BY ...), enabling running totals, moving averages, and per-group rankings.

SELECT name, dept, salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

All assign a position within an ordered partition. ROW_NUMBER gives a unique sequential number even for ties. RANK gives ties the same number but then skips (1,1,3). DENSE_RANK gives ties the same number without gaps (1,1,2). ROW_NUMBER is the go-to for 'pick the latest row per group' (filter to row number 1).

Use a window aggregate ordered by the sequencing column: SUM(amount) OVER (ORDER BY date) accumulates as rows progress. Add PARTITION BY to restart the total per group. The ORDER BY inside OVER plus the default frame (rows up to the current one) is what produces the cumulative behaviour.

An index (typically a B-tree) keeps a sorted structure of column values pointing to rows, turning a full table scan into a fast logarithmic lookup for equality, range, and sort operations. The cost is extra storage and slower writes, because every insert/update/delete must maintain the index. So you index columns used in WHERE, JOIN, and ORDER BY, but not everything.

A composite index is sorted by its columns left to right, so it can be used only for predicates that include a leftmost prefix — an index on (a, b, c) helps queries filtering on a, or a and b, but not on b alone. You order columns by selectivity and how queries filter, putting equality columns before range columns. This 'leftmost prefix' rule is central to index design.

Common reasons: wrapping the column in a function or doing arithmetic on it (WHERE YEAR(date) = ...), a leading-wildcard LIKE '%x', type mismatches forcing a cast, low selectivity where a scan is cheaper, or statistics being stale. Sometimes the optimizer correctly decides a sequential scan is faster. You diagnose with EXPLAIN and fix by rewriting the predicate to be sargable or adding the right index.

Atomicity: a transaction is all-or-nothing. Consistency: it moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions don't interfere as if run alone (to the degree set by the isolation level). Durability: once committed, changes survive crashes. Together they make multi-step operations like transfers safe.

Isolation levels trade consistency against concurrency by controlling which interference is allowed. READ UNCOMMITTED allows dirty reads; READ COMMITTED prevents dirty reads; REPEATABLE READ also prevents non-repeatable reads; SERIALIZABLE prevents phantoms too, behaving as if transactions ran one at a time. Higher levels are safer but reduce concurrency and can cause more blocking or serialization failures.

A view is a saved query that runs each time you select from it — always current, no extra storage, but no speed benefit. A materialized view stores the computed result physically, so reads are fast, but the data is a snapshot that must be refreshed to stay current. You use plain views for abstraction and materialized views to cache expensive aggregations.