sql

Vector search over rows is the wrong tool when the user asks for a count or an aggregate. The signal is retrieving the right schema, generating validated SQL, and knowing when to query the database instead of embedding it.

SCD2 separates people who have run pipelines from people who have read about them. The whole answer hinges on one atomic MERGE that closes the old row and opens the new one, and stays correct when the job retries.

Sessionization separates people who reach for a self-join from people who know LAG plus a running sum. Here is the two-pass pattern that scales to billions of events, plus the edge cases interviewers push 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. The signal is choosing the right ranking function for ties and knowing window frames. Here is the pattern and the tie nuance.

Query tuning is a core data-engineering skill, and the signal is reading the execution plan before changing anything, then applying the right fix. Here is the diagnostic method that separates guessers from engineers.

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

Hierarchies (org charts, bill-of-materials, category trees) need recursion, and a self-join only goes one level deep. The signal is the recursive CTE with its anchor plus recursive members, and knowing how it terminates.

Pivoting (rows to columns) is constant 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 canonical product-analytics SQL question. The signal is grouping users by signup cohort, computing each activity's period offset, and counting distinct returners per offset to build the retention triangle.

Joins are the heart 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 silently doubles your sums.

The subtlety in rolling aggregates is the window frame clause, especially ROWS vs RANGE. The signal is the correct frame for a moving window and the tied-rows gotcha that corrupts running totals.

Means lie on skewed data like latency and spend, and SQL has 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 trip up even senior engineers. The signal is UNION vs UNION ALL and its dedup cost, plus three-valued logic where NULL equals nothing, not even itself.

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

Time-based analysis is everywhere in analytics, and time zones and bucketing are the common traps. The signal is DATE_TRUNC for bucketing, interval math, and storing UTC.

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

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

Exact COUNT(DISTINCT) over billions of rows tracks every unique value, so it gets 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 before it, which a normal join cannot. The signal is recognizing the per-row use cases: top-N per group, table functions, unnesting arrays.

Modern warehouses store JSON natively, and querying it well separates strong data engineers 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.

You cannot filter on a window function in WHERE, so people wrap queries in subqueries. QUALIFY filters window results directly. The signal is knowing why WHERE cannot do it and writing top-N-per-group cleanly.

MERGE handles insert-or-update-or-delete in one statement, the backbone of incremental loads and slowly-changing dimensions. The signal is the matched/not-matched logic and what makes a retry safe.

Modern table formats let you query a table as of a past version or timestamp, the backbone of reproducibility, audits, and recovery. The signal is the versioned-snapshot mechanism and where it breaks.

The single most common way SQL leaks the future into a training set is a careless join to a feature table. Point-in-time correctness is the fix, and it is an as-of join. Here is how to write it.

Funnels look like simple counts until you require the steps happen in order and within a window. Counting each step independently overstates conversion. Here is the ordered-funnel query.

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

You don't always need a dedicated vector database. When embeddings live next to your relational data, nearest-neighbor in SQL makes filters and joins trivial. Here is how, and the scale where it breaks.

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 needs more care still. Here are both, done efficiently.

Defining the label is half the modeling problem, and the SQL hides two leakage traps: looking into the future for features, and a label window that isn't fully observed yet. Here is the correct query.

Bucketing turns 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 often 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 appear in an event table, so a 7-day average silently 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, produce 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 instead of trusting it.

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

Wide tables with a column per period are painless to read and painful 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 except for ROWS vs RANGE return different numbers, and most candidates cannot say why. The signal is knowing RANGE groups by value (peers) while ROWS counts physical rows.

Everyone says 'add an index,' but a strong engineer reads the plan first. The signal is finding the dominant operator, comparing estimated vs actual rows, and recognizing the seq-scan, bad-join, and spill patterns.

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

A flat path extract is easy; an array of objects three levels deep is where people freeze. The signal is LATERAL FLATTEN / UNNEST to explode arrays into rows and a clear rule for when to flatten vs query in place.