SQL for Product Analytics

Why SQL is the gatekeeper

Both target JDs name SQL explicitly. Founding-DS JDs want "expert SQL." Leadership-role JDs want "strong command of SQL, Python, and Git." For product DS loops at AI companies, the SQL screen is almost always the first technical filter — usually one or two timed problems where you live-share a screen, get a schema, and write queries against it on the spot.

The bar isn't "knows SQL." The bar is writes correct SQL fast, with appropriate use of window functions, without flinching at gotchas. Interviewers are looking for three signals:

1. You reach for window functions before self-joins, when both work.
2. You handle NULLs and dedupe deliberately, not by accident.
3. You can read a schema and ask one or two sharp clarifying questions before writing anything.

Window functions

Window functions are the dividing line between "SQL fluent" and "SQL strong" for analytics. Everything else in this chapter rests on them. The four families to know cold:

Ranking: ROW_NUMBER, RANK, DENSE_RANK

Pick exactly one row per group ("most recent order per user"), or rank within groups ("top 3 products by revenue per category"). The difference matters in tie cases:

-- Most recent order per user
SELECT user_id, order_id, ordered_at
FROM (
  SELECT
    user_id,
    order_id,
    ordered_at,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

-- ROW_NUMBER: ties broken arbitrarily, exactly 1 row per group
-- RANK:       ties share a rank, next rank skips (1,2,2,4)
-- DENSE_RANK: ties share a rank, next rank doesn't skip (1,2,2,3)

Offset: LAG, LEAD

Compare a row to the previous or next row in its partition. Use for diffs, retention, churn detection, time-between-events.

SELECT
  user_id,
  ordered_at,
  LAG(ordered_at) OVER (PARTITION BY user_id ORDER BY ordered_at) AS prev_order_at,
  DATE_DIFF('day',
    LAG(ordered_at) OVER (PARTITION BY user_id ORDER BY ordered_at),
    ordered_at) AS days_since_prev
FROM orders;

Aggregate windows: SUM, AVG, COUNT with OVER

Running totals, moving averages, share-of-total. Frame clauses matter — default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when you specify ORDER BY, which trips people up.

WITH daily AS (
  SELECT
    event_date,
    COUNT(DISTINCT user_id) AS dau
  FROM events
  GROUP BY event_date
)
SELECT
  event_date,
  dau,
  AVG(dau) OVER (
    ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS dau_7d_avg
FROM daily
ORDER BY event_date;

First/last: FIRST_VALUE, LAST_VALUE, NTH_VALUE

Carry a value from the first/last row of a partition into every other row. Useful for "what was the user's first plan?" or "what was the last touchpoint before conversion?"

SELECT
  user_id,
  event_at,
  plan,
  FIRST_VALUE(plan) OVER (
    PARTITION BY user_id
    ORDER BY event_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_plan
FROM plan_changes;

Cohort retention

"Of users who signed up in week N, how many came back in week N+k?" — the question every product DS gets asked. Two versions: classical retention (came back at all by week N+k) and n-day return (came back exactly on day N+k).

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('week', signup_at) AS cohort_week
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('week', event_at) AS activity_week
  FROM events
)
SELECT
  c.cohort_week,
  DATE_DIFF('week', c.cohort_week, a.activity_week) AS weeks_since_signup,
  COUNT(DISTINCT c.user_id) AS retained_users,
  COUNT(DISTINCT c.user_id) * 1.0 /
    FIRST_VALUE(COUNT(DISTINCT c.user_id)) OVER (
      PARTITION BY c.cohort_week
      ORDER BY DATE_DIFF('week', c.cohort_week, a.activity_week)
    ) AS retention_rate
FROM cohorts c
LEFT JOIN activity a
  ON c.user_id = a.user_id
  AND a.activity_week >= c.cohort_week
GROUP BY 1, 2
ORDER BY 1, 2;

The denominator trick (FIRST_VALUE over the cohort partition) avoids the second pass that beginner versions of this query use.

Funnels

"Of users who hit step 1, how many reached step N?" — with an ordering constraint (step N must come after step N-1 in time, for the same user). The naive self-join answer is O(steps²); the right answer uses a window function:

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event = 'signup'  THEN event_at END) AS signup_at,
    MAX(CASE WHEN event = 'upload'  THEN event_at END) AS upload_at,
    MAX(CASE WHEN event = 'publish' THEN event_at END) AS publish_at
  FROM events
  GROUP BY user_id
)
SELECT
  COUNT(*)                                                                  AS signup_count,
  COUNT(CASE WHEN upload_at  > signup_at THEN 1 END)                        AS upload_count,
  COUNT(CASE WHEN publish_at > upload_at AND upload_at > signup_at THEN 1 END) AS publish_count
FROM user_steps;

Two refinements interviewers will probe:

• First occurrence vs any occurrence. If a user signs up, uploads, signs up again (via referral?), then uploads — does the second upload count? Usually you want MIN() for signup and the next-step MIN() that follows it. Be explicit about the rule.
• Time-window funnels. "Reached step N within 7 days of step N-1" requires keeping the previous step's timestamp via window function and filtering on the diff.

Sessionization

Group consecutive events into sessions based on a gap rule ("if more than 30 minutes elapsed, start a new session"). The canonical pattern uses LAG + a running sum on the "is this a new session" flag:

WITH gapped AS (
  SELECT
    user_id,
    event_at,
    CASE
      WHEN DATE_DIFF('minute',
                     LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at),
                     event_at) > 30
        OR LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) IS NULL
      THEN 1 ELSE 0
    END AS is_new_session
  FROM events
),
sessioned AS (
  SELECT
    user_id,
    event_at,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_at) AS session_id
  FROM gapped
)
SELECT
  user_id,
  session_id,
  MIN(event_at) AS session_start,
  MAX(event_at) AS session_end,
  COUNT(*)      AS event_count
FROM sessioned
GROUP BY 1, 2;

Gaps and islands

The classical pattern: given a sequence of events, group consecutive runs (subscribers continuously active week-over-week, days a user logged in consecutively). The trick is the "tabibitosan" identity — subtract a running rank from the date to collapse runs into the same group:

WITH daily AS (
  SELECT DISTINCT user_id, DATE_TRUNC('day', event_at) AS login_day
  FROM events
),
grouped AS (
  SELECT
    user_id,
    login_day,
    DATE_DIFF('day', '1970-01-01', login_day) -
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) AS streak_key
  FROM daily
)
SELECT
  user_id,
  MIN(login_day) AS streak_start,
  MAX(login_day) AS streak_end,
  COUNT(*)       AS streak_days
FROM grouped
GROUP BY user_id, streak_key
ORDER BY user_id, streak_start;

Read carefully: the streak_key is "date number minus rank". For consecutive dates, both increase by 1, so the difference is constant within a run — and changes only at the gap. Same trick with generate_series for "find missing dates."

The gotchas interviewers love

If they're testing you, they're testing these:

NULL semantics

• COUNT(*) counts NULLs, COUNT(col) doesn't. COUNT(DISTINCT col) ignores NULLs.
• col != 'x' excludes NULL rows. If you want them, write col IS DISTINCT FROM 'x' (or COALESCE(col,'') != 'x' in older dialects).
• NOT IN (subquery) returns zero rows if any subquery row is NULL. Use NOT EXISTS.

Dedupe before you join

If your join key isn't unique on both sides, you'll silently multiply rows. Dedupe with ROW_NUMBER() or DISTINCT explicitly. A sanity check interviewers love: "what's the row count of left, right, joined? Does the joined count make sense?"

Time-zone and DST

Always say which time zone you're truncating to. DATE_TRUNC('day', event_at) in UTC vs the user's local zone produces different daily metrics. The right answer is usually "use the customer's reporting zone, store UTC in the warehouse, convert in the metric query."

Group-by-with-CASE for pivots

Long-to-wide reshapes use SUM(CASE WHEN ... THEN ... END). Don't reach for PIVOT unless your dialect supports it and your interviewer specifically asks.

Interview probes

The questions you should be able to answer cold:

✓ Check yourself

Answer from memory before opening each one.

1. A 7-day moving average of DAU uses AVG(dau) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). The daily table has no row for Saturdays in three weeks because nothing was logged. What does the value on those Sundays cover, and how do you fix it?

   Show answer

   A ROWS frame counts rows, so on those Sundays it spans eight calendar days and leaves out a real zero for Saturday. Build a date spine, left join the counts and fill missing days with zero, or use a RANGE frame with an interval offset where the dialect allows it. See window functions.

2. A user logged in on Jan 1, 2, 3, 5, 6 and 9. Using the date-number-minus-row-number trick, what key does each login get, and how many streaks and of what lengths come out?

   Show answer

   Subtracting the row number (1 to 6) from the day number gives the same value for the first three days, a second value for the 5th and 6th, and a third for the 9th. That is three streaks of 3, 2 and 1 days. The key is constant inside a run because both terms rise by one, and it jumps at a gap. See gaps and islands.

3. A weekly retention query joins users (one row per user) to events (many rows per user) and uses COUNT(*). For a cohort of 1M users, week 1 shows 40M “retained.” What happened, and what do you change?

   Show answer

   The join fans out: each user is repeated once per event. Reduce activity to distinct (user, week) pairs first and count distinct users, then check the row counts of left, right and joined before trusting the number. See cohort retention and dedupe before you join.

4. A funnel query takes MAX(event_at) for each step. One user signs up on day 1, uploads on day 2, signs up again through a referral on day 3 and never uploads again. What does the funnel say about this user, and what rule should you state instead?

   Show answer

   The signup is anchored on day 3, so the day-2 upload comes before it and the user drops out of the upload step, which undercounts. State the rule: usually the first signup, then the first following event for each later step, with a time window if the question asks for one. See funnels.

5. One user has events at 10:00, 10:20, 10:55 and 11:10 under a 30-minute gap rule. How many sessions are there, and which column does the session number come from?

   Show answer

   Two sessions: the gap from 10:20 to 10:55 is 35 minutes, and the others are 20 and 15. The session number is a running sum of the new-session flag, where the flag is 1 on the first event (LAG is NULL) and after any gap above 30 minutes. See sessionization.

6. The warehouse stores UTC. A customer in Sydney asks for daily active users, and the team wants one definition across every dashboard. What do you choose, and what changes if you pick the other option?

   Show answer

   Store UTC, and truncate to day in the customer’s reporting zone inside the metric query, stating that zone. Truncating in UTC shifts activity across day boundaries and changes daily counts and any day-over-day comparison, and daylight-saving changes make the shift uneven. See time zone and DST.

Takeaway

• Window functions are the baseline for product SQL: reach for them before self-joins, and know what the default frame does.
• A ROWS frame counts rows, not days, so a moving average over days needs one row per day or a RANGE interval.
• Retention counts distinct users per cohort and week, and week zero of the cohort is the denominator.
• A funnel needs a stated rule for repeated events, usually the first occurrence and the first step that follows, plus a time window when asked.
• Sessions come from LAG, a new-session flag and a running sum; streaks come from date number minus row number.
• Row counts of both inputs and the joined result are the cheapest check against fan-out, and NULLs and time zones need handling on purpose.

The experiment design that puts these queries to work comes next. → Experimentation Foundations

Further reading

• Window Functions (tutorial) — PostgreSQL Documentation, accessed 2026. A short worked introduction to partitions, ordering and frames.
• Value Expressions: Window Function Calls — PostgreSQL Documentation, accessed 2026. The exact definition of frames, the default frame and ROWS versus RANGE.
• Window function calls — Google Cloud, accessed 2026. BigQuery’s frame rules, which differ from PostgreSQL on RANGE offsets.
• Window Functions — DuckDB documentation, accessed 2026. A compact reference for the ranking, offset and aggregate functions used in this chapter.
• Subquery Expressions — PostgreSQL Documentation, accessed 2026. States how NOT IN and EXISTS treat NULL, the source of the zero-row surprise.