window functions

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.