sql

Vector search over rows is the wrong tool when the user asks for a count or an aggregate. Interviewers want to see you retrieve the right schema, generate validated SQL, and know when to query the database instead of embedding it.

SCD2 tells apart engineers who have actually run pipelines from those who have only read about them. The answer rests on a single atomic MERGE that closes the prior row while opening the new one, and holds up when the job retries.

Sessionization tells apart candidates who reach for a self-join from those who know LAG plus a running sum. Below is the two-pass pattern that holds up across billions of events, along with the edge cases interviewers press on.

Top-N-per-group is the window-function question every data round asks, and the trap is RANK vs ROW_NUMBER vs DENSE_RANK. What interviewers want is picking the right ranking function for ties and understanding window frames. Here is the pattern and the tie nuance.

Query tuning is a core data-engineering skill, and what interviewers watch for is reading the execution plan before you change anything, then applying the right fix. Here is the diagnostic method that tells guessers apart from engineers.

Dedup is a daily data-engineering task and a frequent screen. The signal is ROW_NUMBER to keep the right record (not just DISTINCT), pinning down what 'duplicate' actually means, and handling near-duplicates at scale.

Hierarchies (org charts, bill-of-materials, category trees) call for recursion, and a self-join reaches only one level down. The signal is the recursive CTE with its anchor plus recursive members, and understanding how it terminates.

Pivoting (rows to columns) shows up constantly in analytics and reporting, and the portable trick is conditional aggregation, not a vendor PIVOT clause. The signal is the CASE-inside-aggregate pattern and knowing its one real limit.

The classic product-analytics SQL question. The signal is grouping users by signup cohort, deriving each activity's period offset, and counting distinct returners per offset to build the retention triangle.

Joins are the core of SQL, and the real signal is precise semantics: semi/anti joins (EXISTS / NOT EXISTS), the NOT IN null trap, and the fan-out bug that quietly doubles your sums.

The subtle part of rolling aggregates is the window frame clause, especially ROWS vs RANGE. The signal is the right frame for a moving window and the tied-rows gotcha that corrupts running totals.

Means mislead on skewed data like latency and spend, and SQL ships dedicated functions for the truth. The signal is PERCENTILE_CONT/DISC for exact quantiles, NTILE for bucketing, and why p50/p99 beat the average.

Set operations and NULL semantics catch out even senior engineers. The signal is UNION vs UNION ALL and its dedup cost, plus three-valued logic where NULL matches nothing, not even itself.

Materialized views swap storage and freshness for query speed by precomputing results. The signal is grasping the view-vs-materialized-view tradeoff and the refresh/staleness question.

Time-based analysis runs through all of analytics, and time zones and bucketing are the usual traps. The signal is DATE_TRUNC for bucketing, interval math, and storing UTC.

Correlated subqueries read naturally but may run once per row (O(n squared)), a classic slow-query cause. The signal is spotting them and rewriting as joins or window functions.

Reporting frequently needs subtotals and grand totals across several dimensions, and stitching that together with many UNIONs wastes work. The signal is GROUPING SETS, ROLLUP, and CUBE in one pass.

Exact COUNT(DISTINCT) over billions of rows has to track every unique value, so it turns slow and expensive. The signal is knowing when a sketch like HyperLogLog buys huge memory and speed wins for a sub-percent error budget.

A LATERAL join lets a FROM-clause subquery reference columns from the table ahead of it, which a normal join cannot. The signal is spotting the per-row use cases: top-N per group, table functions, unnesting arrays.

Modern warehouses store JSON natively, and querying it well sets strong data engineers apart from the rest. The signal is path access plus unnesting, and a clear call on when to flatten hot fields versus keep the schema-on-read flexibility.

WHERE cannot reference a window function, so people resort to wrapping queries in subqueries. QUALIFY filters on window output directly. The signal is explaining why WHERE fails and writing top-N-per-group cleanly.

MERGE does insert, update, or delete in a single statement, the foundation of incremental loads and slowly-changing dimensions. The signal is the matched/not-matched logic and what keeps a retry safe.

Modern table formats can query a table as of an earlier version or timestamp, the foundation of reproducibility, audits, and recovery. The signal is the versioned-snapshot mechanism and where it falls apart.

The most frequent way SQL leaks the future into a training set is a sloppy join to a feature table. The fix is point-in-time correctness, implemented as an as-of join. Here is how to write it.

Funnels seem like plain counts until you demand the steps occur in order and within a window. Tallying each step on its own overstates conversion. Here is the ordered-funnel query.

Consecutive-run problems (active streaks, uptime windows, price-stable periods) all boil down to one trick. Once the row-number difference clicks, they collapse to a window function and a group-by.

A dedicated vector database is not always required. When embeddings sit beside your relational data, nearest-neighbor in SQL makes filters and joins easy. Here is how, and the scale where it breaks down.

Building a training set from a billion-row table means sampling, and the naive 'ORDER BY random() LIMIT n' sorts the whole table. Stratified sampling takes even more care. Here are both, done efficiently.

Defining the label is half the modeling problem, and the SQL conceals two leakage traps: reaching into the future for features, and a label window not yet fully observed. Here is the correct query.

Bucketing converts a skewed continuous feature into something a model (or a report) handles well, and the choice between equal-width and equal-count bins changes everything on skewed data. Here is both in SQL.

Production drift monitoring frequently lives in the warehouse, not a fancy tool. Comparing two distributions in SQL with a metric like PSI is a few CTEs. Here is how to compute it.

Days with zero events simply don't show up in an event table, so a 7-day average quietly averages the wrong 7 rows. A date spine makes the missing days explicit. Here is the pattern.

The applied data-wrangling screen: here is a grubby database, turn out a clean analysis-ready table. The signal is profiling before transforming, doing set-based cleaning in SQL and row-level fixes in Python, joining without fanning out rows, and validating the output rather than trusting it.

An org-chart recursion walks a tree, but a general graph has multiple paths and back-edges. The signal is building up the visited path to prune cycles and ranking paths by cost to land the shortest one.

Wide tables with a column per period are easy to read and hard to query. The signal is unpivoting to long format with UNPIVOT or a UNION/cross-join trick, and knowing how NULLs and types bite you.

Two queries that look identical apart from ROWS vs RANGE return different numbers, and most candidates cannot explain why. The signal is knowing RANGE groups by value (peers) while ROWS counts physical rows.

The reflex is 'add an index,' but a good engineer reads the plan before touching anything. The signal is spotting the operator that dominates runtime, comparing estimated against actual rows, and knowing the seq-scan, bad-join, and spill patterns.

Overlapping subscription or session windows have to collapse into clean periods, and a naive self-join runs quadratic. The signal is the gaps-and-islands trick: a running max end-time that marks where each new island begins.

Pulling a scalar by path is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows plus a clear rule for when to flatten versus query in place.