SQL for Data Engineers: Product Analytics Pipelines

In Snowflake:

SELECT
    e.event_id,
    e.properties:page::STRING             AS page,
    TRY_TO_DOUBLE(e.properties:value::STRING) AS order_value,
    e.properties:referrer::STRING         AS referrer,
    f.value:sku::STRING                   AS sku,
    f.value:qty::INT                      AS qty
FROM raw_events e,
     LATERAL FLATTEN(input => e.properties:items) f
WHERE e.event_name = 'purchase';

The same logic in portable SQL, as run on DuckDB to produce the output above:

SELECT
    e.event_id,
    e.properties ->> '$.page'                         AS page,
    TRY_CAST(e.properties ->> '$.value' AS DOUBLE)    AS order_value,
    e.properties ->> '$.referrer'                     AS referrer,
    item ->> '$.sku'                                  AS sku,
    CAST(item ->> '$.qty' AS INT)                     AS qty
FROM raw_events e,
     unnest(CAST(e.properties -> '$.items' AS JSON[])) AS t(item)
WHERE e.event_name = 'purchase'
ORDER BY e.event_id, sku;

• Path extraction plus a cast: ->> '$.page' (DuckDB and Postgres) or properties:page::STRING (Snowflake) pulls a field out as text, and a cast types it. A missing key, like e2's referrer, comes back as NULL rather than an error.
• Values arrive with mixed types. e2's value is the string "15" where e1's is a number. TRY_CAST (or Snowflake's TRY_TO_DOUBLE) handles both and nulls out garbage; a plain cast on a JSON field fails or behaves differently per engine.
• Arrays become rows with a lateral FLATTEN (Snowflake docs) or UNNEST. e1 had two items, so it produces two rows, which changes the grain from one row per event to one row per item. Any event-level number (order_value) now repeats per item, so don't sum it at this grain.
• Flatten once, at ingestion. Parsing JSON in every downstream query is slow and repeats the type decisions; a typed Silver table does it once.

Engineer check. Grain: one row per purchased item, not per event. Run twice: read-only. NULL: missing keys are NULL; an event with an empty or missing items array disappears from an inner FLATTEN/UNNEST, so use OUTER => TRUE (Snowflake) or a LEFT JOIN if those events must stay.

SELECT app_version,
       COUNT(*)                                                         AS raw_events,
       COUNT(DISTINCT event_id)                                         AS unique_events,
       ROUND(100.0 * (COUNT(*) - COUNT(DISTINCT event_id)) / COUNT(*), 1) AS duplicate_pct
FROM raw_events
GROUP BY app_version
ORDER BY app_version;

• COUNT(*) − COUNT(DISTINCT event_id) is the number of extra copies; dividing by COUNT(*) gives the share of the stream that's duplication.
• Version 3.3 is 60% duplicates against 0% for 3.2, which points at a retry bug in that release. That's worth an alert, not just a dedup step.
• Why streams are at-least-once: network retries, mobile apps that buffer offline and resend, and collectors that redeliver after a timeout all produce copies, and exactly-once delivery end to end is rare and expensive. So every model built on raw events dedups by event_id first (QUALIFY ROW_NUMBER() … = 1, post 01), and a metric like this one tells you how much that step is hiding.

Engineer check. Grain: one row per app version. NULL: COUNT(DISTINCT …) ignores NULL ids, so events without an event_id look like neither unique events nor duplicates; count them separately.

WITH ordered AS (
    SELECT *, LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts, event_id) AS prev_ts
    FROM events
),
flagged AS (
    SELECT *,
           CASE WHEN prev_ts IS NULL OR event_ts - prev_ts > INTERVAL 30 MINUTE THEN 1 ELSE 0 END AS is_new_session
    FROM ordered
)
SELECT user_id, event_id, event_name, event_ts, is_new_session,
       SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_ts, event_id
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM flagged
ORDER BY user_id, event_ts;

• LAG(event_ts) gives each event the user's previous event time.
• The new-session flag is 1 for a user's first event (no previous) or when the gap is over 30 minutes. u1's 10:30 event comes 65 minutes after 09:25, so it starts session 2.
• SUM(flag) OVER (… ROWS UNBOUNDED PRECEDING …) turns the flags into a counter: 1, 1, 1, 2, 2. This running-sum-of-boundaries trick is the standard way to number islands of rows.
• ORDER BY event_ts, event_id in both windows makes ties deterministic, and ROWS avoids the peer-row trap from post 03 (Q46).
• For a globally unique id, combine the user and the number, or hash them.

Engineer check. Grain: unchanged, one row per event, now carrying a session number. NULL: the first event's prev_ts is NULL, which the flag handles explicitly; without the IS NULL branch the first session would start at 0.

WITH sessionized AS (
WITH ordered AS (
    SELECT *, LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts, event_id) AS prev_ts
    FROM events
),
flagged AS (
    SELECT *,
           CASE WHEN prev_ts IS NULL OR event_ts - prev_ts > INTERVAL 30 MINUTE THEN 1 ELSE 0 END AS is_new_session
    FROM ordered
)
SELECT user_id, event_id, event_name, event_ts, is_new_session,
       SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_ts, event_id
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM flagged
),
sessions AS (
    SELECT user_id, session_no,
           COUNT(*)                         AS events,
           MIN(event_ts)                    AS session_start,
           MAX(event_ts)                    AS session_end,
           datediff('minute', MIN(event_ts), MAX(event_ts)) AS duration_min,
           COUNT(*) = 1                     AS is_bounce
    FROM sessionized
    GROUP BY user_id, session_no
)
SELECT COUNT(*)                                                       AS sessions,
       ROUND(100.0 * AVG(CASE WHEN is_bounce THEN 1 ELSE 0 END), 1)    AS bounce_rate_pct,
       ROUND(AVG(CASE WHEN NOT is_bounce THEN duration_min END), 1)    AS avg_minutes_engaged
FROM sessions;

• Group by (user_id, session_no) to get one row per session (the grain Q57 created). Its span is MAX − MIN of the event times.
• A bounce is a session with exactly one event. Its duration is 0 by definition, because there is no second event to measure to.
• Averaging a 1/0 flag gives the bounce rate: here 1 of 4 sessions (u2's single page view at 11:00), 25%.
• Exclude bounces from the average duration. Including their zeros drags the average down and mixes two different questions ("how often do people leave immediately?" and "how long do engaged sessions last?").

Engineer check. Grain: the final row summarises all sessions. NULL: AVG of the CASE without an ELSE skips bounces, which is exactly the intent.

WITH v AS (SELECT user_id, MIN(event_ts) AS t FROM events WHERE event_name = 'view_item' GROUP BY user_id),
c AS (SELECT v.user_id, MIN(e.event_ts) AS t FROM v JOIN events e
        ON e.user_id = v.user_id AND e.event_name = 'add_to_cart' AND e.event_ts > v.t GROUP BY v.user_id),
k AS (SELECT c.user_id, MIN(e.event_ts) AS t FROM c JOIN events e
        ON e.user_id = c.user_id AND e.event_name = 'begin_checkout' AND e.event_ts > c.t GROUP BY c.user_id),
p AS (SELECT k.user_id, MIN(e.event_ts) AS t FROM k JOIN events e
        ON e.user_id = k.user_id AND e.event_name = 'purchase' AND e.event_ts > k.t GROUP BY k.user_id),
steps AS (
    SELECT 1 AS step, 'view_item' AS name, COUNT(*) AS users FROM v
    UNION ALL SELECT 2, 'add_to_cart', COUNT(*) FROM c
    UNION ALL SELECT 3, 'begin_checkout', COUNT(*) FROM k
    UNION ALL SELECT 4, 'purchase', COUNT(*) FROM p JOIN v USING (user_id)
              WHERE p.t <= v.t + INTERVAL 24 HOUR
)
SELECT step, name, users,
       ROUND(100.0 * users / NULLIF(LAG(users) OVER (ORDER BY step), 0), 1) AS pct_of_previous
FROM steps
ORDER BY step;

• Each step is found after the previous one: c takes the first cart after the user's first view, k the first checkout after that cart, and so on. A user can only appear in a step's CTE if they appear in the previous one, so the counts can only fall.
• u3 is the order test. Its only cart came before its view, so it has no cart after a view and stops at step 1. The common shortcut, taking MIN(timestamp) of each event type independently and comparing them, gets this case right too, but it fails a user who carted early, viewed, then carted again: the independent minimum picks the early cart and rejects a real conversion. Chaining the steps handles both.
• The 24-hour window applies to the whole funnel: u2 bought 26 hours after its first view, so it reaches checkout but not purchase.
• users / LAG(users) gives step-to-step conversion; NULLIF guards an empty step.
• For a within-session funnel, add session_no from Q57 to every join.

Engineer check. Grain: one row per funnel step. NULL: a user missing a step has no row in that step's CTE, so the counts fall naturally; never COUNT a column that can be NULL for users who didn't get that far.

WITH cohort AS (
    SELECT user_id, CAST(date_trunc('week', signup_ts) AS DATE) AS cohort_week
    FROM users
),
activity AS (
    SELECT DISTINCT c.user_id, c.cohort_week,
           datediff('week', c.cohort_week, CAST(date_trunc('week', e.event_ts) AS DATE)) AS week_no
    FROM events e JOIN cohort c USING (user_id)
)
SELECT c.cohort_week,
       COUNT(DISTINCT c.user_id)                                                  AS cohort_size,
       COUNT(DISTINCT CASE WHEN a.week_no = 0 THEN a.user_id END)                 AS wk0,
       COUNT(DISTINCT CASE WHEN a.week_no = 1 THEN a.user_id END)                 AS wk1,
       COUNT(DISTINCT CASE WHEN a.week_no = 2 THEN a.user_id END)                 AS wk2,
       ROUND(100.0 * COUNT(DISTINCT CASE WHEN a.week_no = 1 THEN a.user_id END)
             / COUNT(DISTINCT c.user_id), 0)                                      AS wk1_pct
FROM cohort c
LEFT JOIN activity a USING (user_id)
GROUP BY c.cohort_week
ORDER BY c.cohort_week;

• The cohort is the signup week, truncated with date_trunc('week', …) (Monday-based in DuckDB, Postgres and by default in Snowflake; check WEEK_START on Snowflake).
• week_no is how many weeks after the cohort week an activity happened. DISTINCT counts a user once per week however many events they had.
• COUNT(DISTINCT CASE WHEN week_no = N …) builds the retention triangle: one column per relative week.
• The LEFT JOIN keeps every user in the cohort's size even if they never came back, which is the denominator retention needs.
• The 10 March cohort can't have a week 2 yet in data that ends on 19 March, so its 0 is "not yet observable", not "nobody returned". Real retention charts blank out cells that are still in the future.

Engineer check. Grain: one row per cohort week. NULL: users with no activity give NULL week_no, which the CASE expressions skip, and they still count in cohort_size.

SELECT
    COUNT(DISTINCT CASE WHEN CAST(event_ts AS DATE) = DATE '2025-03-28' THEN user_id END)     AS dau,
    COUNT(DISTINCT CASE WHEN CAST(event_ts AS DATE) BETWEEN DATE '2025-03-28' - 27
                                                         AND DATE '2025-03-28' THEN user_id END) AS mau_28d,
    ROUND(COUNT(DISTINCT CASE WHEN CAST(event_ts AS DATE) = DATE '2025-03-28' THEN user_id END)::DOUBLE
          / NULLIF(COUNT(DISTINCT CASE WHEN CAST(event_ts AS DATE) BETWEEN DATE '2025-03-28' - 27
                                                                    AND DATE '2025-03-28' THEN user_id END), 0), 2) AS stickiness
FROM events;

• DAU counts distinct users on the day; u1's two events count once.
• The 28-day window is day − 27 through day, which is 28 days inclusive. Off-by-one windows are the classic bug here: − 28 makes it 29 days and would include u5 from 28 February.
• Stickiness = DAU ÷ MAU, the share of the month's users who show up on a given day: 2 ÷ 4 = 0.5. The cast to DOUBLE avoids integer division, and NULLIF an empty month.
• Distinct counts don't add up: you can't sum daily DAUs into MAU, because the same user is active on many days. That's why MAU needs its own distinct count over the window, and Q62 needs a different technique to compute it for every day.

Engineer check. Grain: one row for one date. NULL: users with no events in the window simply aren't counted; there's nothing to coalesce.

WITH days AS (
    SELECT CAST(d AS DATE) AS d
    FROM generate_series(DATE '2025-03-01', DATE '2025-03-10', INTERVAL 1 DAY) t(d)
),
active AS (
    SELECT DISTINCT user_id, CAST(event_ts AS DATE) AS d FROM events
)
SELECT days.d AS as_of,
       COUNT(DISTINCT active.user_id) AS active_7d
FROM days
LEFT JOIN active ON active.d BETWEEN days.d - 6 AND days.d
GROUP BY days.d
ORDER BY days.d;

• Why not a window function: COUNT(DISTINCT user_id) OVER (ORDER BY d ROWS 6 PRECEDING) is rejected by many engines; Postgres, for one, reports that DISTINCT is not implemented for window functions. And summing daily distinct counts over 7 days counts a user once per active day.
• The spine join: each day in days joins to every activity whose date falls in its 7-day range, and COUNT(DISTINCT user_id) counts unique users per range. u1, active on three days, counts once wherever its days overlap a window.
• LEFT JOIN from the spine keeps days with no activity in range, with a count of 0.
• Cost: each activity row joins to 7 days, so the join is 7 times the activity table, which is fine at daily grain. For very large tables, approximate distinct counts (HyperLogLog) are the usual answer.

Engineer check. Grain: one row per calendar day. NULL: an empty window gives COUNT(DISTINCT NULL) = 0, the right answer.

WITH first_purchase AS (
    SELECT user_id, MIN(event_ts) AS purchase_ts FROM events WHERE event_name = 'purchase' GROUP BY user_id
),
before AS (
    SELECT e.*
    FROM events e JOIN first_purchase fp USING (user_id)
    WHERE e.event_ts < fp.purchase_ts
)
SELECT DISTINCT user_id,
       FIRST_VALUE(referrer) OVER w AS first_touch,
       LAST_VALUE(referrer)  OVER w AS last_touch
FROM before
WINDOW w AS (PARTITION BY user_id ORDER BY event_ts
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY user_id;

• Restrict to events before the first purchase (<, not <=, so the purchase itself, with its NULL referrer, isn't the last touch). u1's tiktok visit after buying doesn't count.
• The trap: with ORDER BY and no frame, the default frame ends at the current row (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). LAST_VALUE then returns each row's own referrer, not the partition's last (step 1). FIRST_VALUE happens to be right with the default frame, which is why the bug hides.
• The fix is an explicit frame over the whole partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. The named WINDOW w keeps both functions on the same definition.
• DISTINCT collapses the per-row repeats to one row per user (QUALIFY ROW_NUMBER() … = 1 works too).

Engineer check. Grain: one row per purchasing user. NULL: a NULL referrer on the last touch is returned as NULL; use LAST_VALUE(referrer IGNORE NULLS) if you want the last known referrer.

WITH months AS (
    SELECT CAST(m AS DATE) AS month
    FROM generate_series(DATE '2025-01-01', DATE '2025-04-01', INTERVAL 1 MONTH) t(m)
),
grid AS (                                        -- every user x every month, zero-filled
    SELECT u.user_id, m.month,
           COALESCE(SUM(s.mrr), 0) AS mrr
    FROM (SELECT DISTINCT user_id FROM subscriptions) u
    CROSS JOIN months m
    LEFT JOIN subscriptions s
           ON s.user_id = u.user_id
          AND m.month >= s.started_month
          AND (s.canceled_month IS NULL OR m.month < s.canceled_month)
    GROUP BY u.user_id, m.month
),
moves AS (
    SELECT *, COALESCE(LAG(mrr) OVER (PARTITION BY user_id ORDER BY month), 0) AS prev
    FROM grid
)
SELECT month,
       SUM(mrr)                                                               AS total_mrr,
       SUM(CASE WHEN prev = 0 AND mrr > 0     THEN mrr        ELSE 0 END)     AS new_mrr,
       SUM(CASE WHEN prev > 0 AND mrr > prev  THEN mrr - prev ELSE 0 END)     AS expansion,
       SUM(CASE WHEN mrr > 0 AND mrr < prev   THEN prev - mrr ELSE 0 END)     AS contraction,
       SUM(CASE WHEN prev > 0 AND mrr = 0     THEN prev       ELSE 0 END)     AS churned
FROM moves
GROUP BY month
ORDER BY month;

• The user × month grid is the key move. Without it, u2 simply has no row in March, and LAG never sees its MRR fall to 0, so churn is invisible. The grid gives every user a row in every month, with 0 when they paid nothing.
• month < canceled_month treats the cancellation month as the first month without revenue; pick a convention and keep it.
• LAG(mrr) compares each user's MRR with last month's, and each change goes in exactly one bucket: from zero is new, up is expansion, down but above zero is contraction, to zero is churned.
• The bridge must balance: last month's total + new + expansion − contraction − churned = this month's total. February: 50 + 40 + 30 − 0 − 0 = 120. Check it in the pipeline; if it doesn't hold, a subscription overlaps or a month is missing.

Engineer check. Grain: one row per month. NULL: LAG is NULL in each user's first month; COALESCE(…, 0) makes that month count as new revenue rather than vanishing.

WITH months AS (
    SELECT CAST(m AS DATE) AS month FROM generate_series(DATE '2025-01-01', DATE '2025-04-01', INTERVAL 1 MONTH) t(m)
),
active AS (SELECT DISTINCT user_id, CAST(date_trunc('month', event_ts) AS DATE) AS month FROM events),
grid AS (
    SELECT u.user_id, m.month, (a.user_id IS NOT NULL) AS is_active
    FROM (SELECT DISTINCT user_id FROM events) u
    CROSS JOIN months m
    LEFT JOIN active a ON a.user_id = u.user_id AND a.month = m.month
),
flags AS (
    SELECT *,
           LAG(is_active) OVER (PARTITION BY user_id ORDER BY month) AS was_active,
           BOOL_OR(is_active) OVER (PARTITION BY user_id ORDER BY month
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS ever_before
    FROM grid
)
SELECT user_id, month,
       CASE WHEN is_active AND NOT COALESCE(ever_before, FALSE) THEN 'new'
            WHEN is_active AND was_active                       THEN 'retained'
            WHEN is_active                                      THEN 'reactivated'
            WHEN was_active                                     THEN 'churned'
            ELSE                                                     'inactive' END AS status
FROM flags
ORDER BY user_id, month;

• The month grid (as in Q64) creates rows for inactive months, so the active-to-inactive edge exists to be labelled.
• was_active (LAG) answers "last month?", and ever_before (BOOL_OR over all earlier rows) answers "ever?". Both are needed: new and reactivated are both "active now, inactive last month", and only ever_before tells them apart. u1 in April is reactivated; u3 in February is new.
• The CASE order matters: the first matching branch wins, so new is tested before the more general is_active branches.
• COALESCE(ever_before, FALSE): the frame 1 PRECEDING is empty in each user's first month, so BOOL_OR returns NULL there.
• On Snowflake, BOOLOR_AGG plays the role of BOOL_OR, or use MAX(CASE WHEN is_active THEN 1 ELSE 0 END).

Engineer check. Grain: one row per user per month, exactly one label each. NULL: the first month's LAG and ever_before are NULL; the CASE handles both explicitly.

SELECT month, active_users,
       LAG(active_users) OVER (ORDER BY month) AS prev_users,
       ROUND(100.0 * (active_users - LAG(active_users) OVER (ORDER BY month))
             / NULLIF(LAG(active_users) OVER (ORDER BY month), 0), 1) AS mom_growth_pct
FROM monthly_active
ORDER BY month;

• LAG gives last month's value; January has none, so its growth is NULL ("no prior period"), not 0.
• NULLIF(prev, 0) turns a zero denominator into NULL, and dividing by NULL gives NULL instead of an error. April's growth after the zero month is undefined, and NULL says so honestly; an "infinite growth" number would mislead any chart.
• 100.0 * forces decimal arithmetic. With integers, 30 / 120 is 0 in many engines.
• March's −100% is real: the outage month dropped to zero. Annotate it rather than hide it.

Engineer check. Grain: one row per month. NULL: two different NULLs appear (no previous month, zero previous month); label them in the report if readers need to tell them apart.

WITH first_act AS (
    SELECT user_id, MIN(event_ts) AS activated_ts
    FROM events WHERE event_name = 'activation' GROUP BY user_id
),
hours AS (
    SELECT CAST(date_trunc('month', u.signup_ts) AS DATE)       AS cohort_month,
           datediff('hour', u.signup_ts, f.activated_ts)      AS hours_to_activate
    FROM users u LEFT JOIN first_act f USING (user_id)
)
SELECT cohort_month,
       COUNT(*)                                                         AS cohort_size,
       COUNT(*) - COUNT(hours_to_activate)                              AS never_activated,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY hours_to_activate)   AS median_hours,
       percentile_cont(0.9) WITHIN GROUP (ORDER BY hours_to_activate)   AS p90_hours
FROM hours
GROUP BY cohort_month
ORDER BY cohort_month;

• MIN(event_ts) takes each user's first activation; u3's second one is ignored.
• LEFT JOIN keeps u5, who never activated, in the cohort. Its hours are NULL.
• Percentile functions skip NULLs, so the median and p90 describe only users who activated, while COUNT(*) − COUNT(hours) reports the non-activators separately. Folding them in as some large number would distort the distribution; dropping them would hide the most important fact about the cohort.
• Percentiles, not the mean: time-to-activation is skewed (most users take hours, a few take weeks), and one slow user moves the mean a lot. Here the median is 14 hours while u4 took 240.
• percentile_cont interpolates between values; percentile_disc returns an actual observed value. Snowflake and Postgres support both with this syntax.

Engineer check. Grain: one row per signup month. NULL: non-activators are NULL in the percentile input and counted separately; a cohort where nobody activated returns NULL percentiles, not 0.

CREATE OR REPLACE TEMP TABLE batch AS
SELECT event_id, user_id, event_ts
FROM raw_events
WHERE _ingested_at > (SELECT watermark_ts FROM etl_control WHERE table_name = 'mart_events')
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY _ingested_at) = 1;   -- dupes inside the batch

MERGE INTO mart_events t
USING batch s ON t.event_id = s.event_id                                          -- dupes across batches
WHEN NOT MATCHED THEN INSERT VALUES (s.event_id, s.user_id, s.event_ts);

SELECT DISTINCT CAST(event_ts AS DATE) AS touched_date FROM batch ORDER BY 1;

• Within the batch: QUALIFY ROW_NUMBER() … = 1 collapses e4's two copies.
• Across batches: e2 was loaded yesterday and redelivered today. The batch alone can't know that, so a plain INSERT would duplicate it. MERGE … WHEN NOT MATCHED THEN INSERT checks the target and only inserts events it hasn't seen; an insert-only MERGE like this is the standard append-with-dedup.
• Touched dates come from the batch's event times. e3 happened on 28 February but arrived on 2 March, so 28 February's daily aggregates need recomputing too (Q72).
• Advance the watermark afterwards with the frozen-bound pattern from post 02 (Q27).

Engineer check. Grain: one row per event_id in the mart. Run twice: the MERGE inserts nothing new the second time. NULL: an event with a NULL event_id never matches and is inserted on every run; reject it before the merge.

SELECT u.user_id,
       COUNT(CASE WHEN e.event_name = 'view_item'   THEN 1 END) AS n_view,
       COUNT(CASE WHEN e.event_name = 'add_to_cart' THEN 1 END) AS n_add_to_cart,
       COUNT(CASE WHEN e.event_name = 'purchase'    THEN 1 END) AS n_purchase,
       COUNT(CASE WHEN e.event_name = 'login'       THEN 1 END) AS n_login,
       datediff('day', (SELECT MAX(event_ts) FROM events x WHERE x.user_id = u.user_id),
                TIMESTAMP '2025-03-31 23:59')                     AS days_since_last_event
FROM users u
LEFT JOIN events e
       ON e.user_id = u.user_id
      AND e.event_ts >  TIMESTAMP '2025-03-31 23:59' - INTERVAL 30 DAY
      AND e.event_ts <= TIMESTAMP '2025-03-31 23:59'
GROUP BY u.user_id
ORDER BY u.user_id;

• Start from users with a LEFT JOIN. u3 had nothing in March, and for a churn model the users with no recent activity are the most important rows. Starting from events would drop them.
• The window filter is in the ON clause. In the WHERE, it would throw away u3's NULL-joined row and turn the left join back into an inner join, a very common mistake.
• COUNT(CASE …) pivots event types into feature columns, and counts 0 where there's nothing.
• Days since last event looks at all history, not just the window: u3 last appeared 75 days before the as-of date, which is the strongest churn signal in the table.
• A fixed as-of date (not CURRENT_DATE) makes features reproducible: training data must be computed as of the label date, or the model learns from the future.

Engineer check. Grain: one row per user. NULL: users with no events at all would get a NULL days_since_last_event; fill it with a large sentinel or a separate flag before training.

WITH nxt AS (
    SELECT event_name,
           LEAD(event_name) OVER (PARTITION BY user_id, session_no ORDER BY event_ts) AS next_event
    FROM sessionized
)
SELECT COALESCE(next_event, '(exit)') AS next_event, COUNT(*) AS occurrences
FROM nxt
WHERE event_name = 'add_to_cart'
GROUP BY 1
ORDER BY occurrences DESC, next_event;

• LEAD(event_name) returns the next event in the partition. Partitioning by (user_id, session_no) stops u2's cart at 11:00 from "leading" to its next session's view_item two hours later, which would be a different visit.
• The last event of a session has no next, so LEAD returns NULL, which COALESCE labels (exit). Dropping NULLs instead would hide every cart abandonment, often the most important path.
• The same LEAD-then-group shape builds a full next-step (Markov) table: group by (event_name, next_event) instead of filtering on one event.

Engineer check. Grain: one row per next event. NULL: the session-ending NULL is the exit signal, so it is kept and labelled.

WITH RECURSIVE tree AS (
    SELECT user_id, 1 AS depth, ['u1', user_id] AS path        -- base: u1's direct referrals
    FROM referrals
    WHERE referred_by = 'u1'
    UNION ALL
    SELECT r.user_id, t.depth + 1, list_append(t.path, r.user_id)
    FROM tree t
    JOIN referrals r ON r.referred_by = t.user_id
    WHERE NOT list_contains(t.path, r.user_id)                   -- cycle guard
      AND t.depth < 20                                           -- depth guard
)
SELECT user_id, MIN(depth) AS depth
FROM tree
GROUP BY user_id
ORDER BY depth, user_id;

• The base case is u1's direct referrals at depth 1. The recursive step joins the rows found so far to the users they referred, adding 1 to the depth, and repeats until a step adds nothing.
• The cycle guard: the bad row (u1 referred by u5) closes a loop, u1 → u2 → u4 → u5 → u1 → u2 … Without a guard the recursion would go round it forever, or until the engine's recursion limit stops it with an error. Carrying the path of visited users and refusing to step onto a user already in it breaks the loop. A depth cap is a second safety net.
• MIN(depth) per user: with bad data a user can be reached by two routes; report the shortest.
• On Snowflake, build the path with ARRAY_APPEND and test it with ARRAY_CONTAINS; on Postgres, use an array and <> ALL(path), or the CYCLE clause, which Postgres 14 added for exactly this (docs).

Engineer check. Grain: one row per downstream user. NULL: a NULL referred_by marks a root and never matches the join, which is correct.

DELETE FROM gold_daily_active WHERE activity_date IN (SELECT d FROM touched);

INSERT INTO gold_daily_active
WITH first_seen AS (                           -- over ALL history, not just the batch
    SELECT user_id, MIN(CAST(event_ts AS DATE)) AS first_day
    FROM mart_events GROUP BY user_id
),
day_users AS (
    SELECT DISTINCT CAST(event_ts AS DATE) AS d, user_id
    FROM mart_events
    WHERE CAST(event_ts AS DATE) IN (SELECT d FROM touched)
)
SELECT du.d,
       COUNT(*)                                        AS active_users,
       COUNT(CASE WHEN fs.first_day = du.d THEN 1 END) AS new_users,
       COUNT(CASE WHEN fs.first_day < du.d THEN 1 END) AS returning_users
FROM day_users du
JOIN first_seen fs USING (user_id)
GROUP BY du.d;

• Recompute only the touched dates, with delete-then-insert, so a re-run replaces the same rows with the same values. The build ran it twice.
• first_seen is computed over all history. A user's first day is a global fact, not a fact about the batch. Here the late event moves u2's first day from 2 March to 1 March: 1 March gains a new user, and on 2 March u2 changes from new to returning. Both dates are touched, so both are rebuilt correctly.
• The trap: if a late event changes a user's first day to a date that isn't in the batch's touched list, an earlier date's split goes stale. Late events always touch their own date, which is the date that gains the new user, so the touched list covers this case; but if you ever derive touched differently, add every date where an affected user was previously counted as new.
• This combines the three incremental patterns of the series: a watermark picks the batch, the touched dates scope the work, and delete-then-insert makes it idempotent.

Engineer check. Grain: one row per date. Run twice: verified unchanged. NULL: users whose events all have NULL timestamps have no first day and are dropped by the inner join; reject such events upstream.