Stamp each event with the session it belongs to: LAG the gap, flag a new session past the timeout, running-sum the flag into a session id.

Anchor cohorts on first activity, pin one definition of retained, and keep the FULL cohort as the denominator with a LEFT JOIN.

Collapse an event log to one row per user with MAX(CASE) step flags, enforce step order with timestamps, and read conversion between steps.

The LAST_VALUE default-frame trap, ROWS vs RANGE on ties, why a window cannot go in WHERE, NTILE, and the QUALIFY rewrite.

Shred JSON into typed columns with the path operator, explode nested arrays with json_each, and avoid the double-count trap.

The three fact grains, and the one that mutates: build an accumulating-snapshot fact with NULLable milestone dates, computed lags, and an idempotent rebuild.

Join a fact to the dimension version that was valid at event time using a half-open effective range, not the is_current row.

A join to the many side inflates a SUM; fix it by aggregating to the fact grain first, define the metric once, and surface hot keys.

Apply an I/U/D change stream to a target: dedup to the latest version per key, upsert inserts and updates, delete tombstones, stay idempotent.

Load only rows newer than a stored watermark, catch late arrivals with a partition-overwrite backfill, and stay idempotent per partition.

Stage a batch to audit, run freshness/volume/null-rate violation checks, and publish to the served table only when every check returns zero.

Make the four un-runnable system-design mechanisms concrete: sargable pruning, Kafka consumer lag, event-time tumbling windows, and DAG eligibility.

The whole level in one build: Bronze JSON to a deduped, sessionized Silver to a reconciling Gold aggregate, idempotent end to end.