SQL Window Functions for Real Product Analytics

Most data scientists know SELECT, GROUP BY and JOIN. Window functions unlock deeper analytical work and appear frequently in senior data interviews.

This post covers five window-function patterns that frequently support product analytics, using reproducible retention examples.

What Is a Window Function?

FUNCTION_NAME() OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN ...
)

1. ROW_NUMBER — Deduplicate and Rank

The most common use case: find the first event per user, or deduplicate records.

SELECT
  user_id,
  event_name,
  event_time,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_time ASC
  ) AS event_rank
FROM events;

To get only the first event per user:

SELECT * FROM (
  SELECT
    user_id,
    event_name,
    event_time,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY event_time ASC
    ) AS rn
  FROM events
) ranked
WHERE rn = 1;

Interview trap: interviewers often ask "the most recent purchase per user." Same pattern — flip ORDER BY to DESC, filter WHERE rn = 1.

2. LAG and LEAD — Compare Rows Across Time

LAG gives the value from the previous row. LEAD gives the next row. Both are essential for session analysis and churn detection.

SELECT
  user_id,
  event_time,
  LAG(event_time) OVER (
    PARTITION BY user_id
    ORDER BY event_time
  ) AS previous_event_time,
  EXTRACT(EPOCH FROM (
    event_time - LAG(event_time) OVER (
      PARTITION BY user_id ORDER BY event_time
    )
  )) / 3600 AS hours_since_last_event
FROM events;

If hours_since_last_event is greater than 24, the user had a gap in activity. Useful for defining session boundaries and detecting at-risk users.

3. SUM OVER — Running Totals

Running totals track cumulative revenue, signups, or events over time.

SELECT
  DATE(event_time) AS date,
  COUNT(DISTINCT user_id) AS daily_signups,
  SUM(COUNT(DISTINCT user_id)) OVER (
    ORDER BY DATE(event_time)
  ) AS cumulative_signups
FROM events
WHERE event_name = 'signup'
GROUP BY DATE(event_time)
ORDER BY date;

The SUM wraps the aggregate COUNT. Common interview mistake — people forget you can nest aggregates inside window functions.

4. RANK vs DENSE_RANK — Leaderboards and Ties

RANK skips numbers after a tie. DENSE_RANK does not.

SELECT
  user_id,
  SUM(amount) AS total_spend,
  RANK() OVER (ORDER BY SUM(amount) DESC) AS spend_rank,
  DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank
FROM purchases
GROUP BY user_id
ORDER BY total_spend DESC;

Two users tied at rank 1 — RANK gives the next user rank 3, DENSE_RANK gives rank 2. Use DENSE_RANK when gaps in ranking don't make sense for your use case.

5. NTILE — Segment Users into Buckets

NTILE divides rows into N equal buckets. Classic use: top 10% spenders, bottom quartile by engagement.

SELECT
  user_id,
  total_spend,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT user_id, SUM(amount) AS total_spend
  FROM purchases
  GROUP BY user_id
) user_spend;

Decile 1 = top 10% spenders. Decile 10 = bottom 10%. Now compare retention and churn across spend segments — standard product analytics workflow.