# The Space Between

> The time between events is the tell.

Canonical URL: <https://datadriven.io/problems/the_space_between>

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

An onboarding team measures user momentum as the average gap, in seconds, between each of a user's events and the one immediately before it, where event_timestamp is stored as a Unix epoch second. For each user with more than one recorded event, return that average gap, ignoring events with no user attached.

## Worked solution and explanation

### What this problem really is

Strip the onboarding language and this is a per-user consecutive difference: the gap from each event to the one before it, averaged inside each user. Because `event_timestamp` is already a Unix epoch second, the gap is a plain subtraction, so no one loses points on date parsing. The real skill is the window. Leave off PARTITION BY `user_id` and the look-back computes gaps across strangers, so a user's first event borrows some other user's last timestamp. The query still runs and still returns numbers, which is exactly what makes it dangerous in an interview.

---

### Break down the requirements

#### Step 1: Look back within each user

Use LAG over `event_data` partitioned by `user_id` and ordered by `event_timestamp` (with `event_id` as a tiebreaker) to pull each event's preceding timestamp for that same user. Filter out rows where `user_id` IS NULL first, because a missing user is not a user and would otherwise form its own bogus partition.

#### Step 2: Take the gap

Compute `event_timestamp` - `prev_timestamp`. Both are epoch seconds, so the difference is already the number of seconds between the two events. No cast, no date parsing, no unit juggling.

#### Step 3: Drop each user's first event

Keep only rows where `prev_timestamp` IS NOT NULL. That drops every user's first event, and a user with a single event yields zero gap rows and disappears entirely, which is precisely the 'more than one event' filter, for free.

#### Step 4: Average per user

GROUP BY `user_id` and AVG the per-event gaps. The average is taken only over the surviving gap rows, so users who never produced a gap contribute nothing.

#### Step 5: Order for the team

ORDER BY `user_id` at the outer layer for deterministic output. The window's own sort is internal to each partition, not the final ordering.

---

### The solution

**PER-USER AVG GAP VIA LAG**

```sql
WITH event_gaps AS (
  SELECT
    user_id,
    event_timestamp,
    LAG(event_timestamp) OVER (
      PARTITION BY user_id ORDER BY event_timestamp, event_id
    ) AS prev_timestamp
  FROM event_data
  WHERE user_id IS NOT NULL
)
SELECT
  user_id,
  AVG(event_timestamp - prev_timestamp) AS avg_progression_seconds
FROM event_gaps
WHERE prev_timestamp IS NOT NULL
GROUP BY user_id
;
```

> **Cost Analysis**
>
> 300M rows. The window does one partition sort per `user_id`, so O(N log N) total in a single pass. A self-join on `event_data` e1 JOIN `event_data` e2 ON `e1.user_id` = `e2.user_id` AND `e2.event_timestamp` > `e1.event_timestamp` is O(N^2) inside each user and will OOM on a heavy user. Partition pruning on `event_timestamp` still applies if the team scopes a date range.

> **Interviewers Watch For**
>
> Ask aloud whether 'consecutive' means `event_timestamp` order or an `event_type` workflow order. The ask says timestamp, but voicing the ambiguity earns points. Then ask what happens when two events share a timestamp for one user, because tied timestamps make the look-back nondeterministic without a tiebreaker like `event_id`.

> **Common Pitfall**
>
> Omitting PARTITION BY `user_id` and writing only ORDER BY `event_timestamp`. The window then spans users, so a user's first event takes its `prev_timestamp` from another user's last event. The query runs, returns plausible numbers, and is entirely wrong.

> **The Elegant Move**
>
> The timestamps are epoch seconds, so the gap is just `event_timestamp` - `prev_timestamp`. Reaching for a cast to a timestamp type and EXTRACT(EPOCH ...) is a Postgres reflex that this engine does not support, and it only invites sign flips and parsing bugs. Subtract the two integers and you are done.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you compute the median gap per user instead of the mean? _(Probes whether you know `PERCENTILE_CONT` or an NTILE trick, and that AVG hides outliers in onboarding funnels.)_
- What changes if you only care about gaps between specific `event_type` pairs, like signup to first action? _(Tests whether you switch to a pre-filtered CTE or a conditional look-back, and how that interacts with the partition.)_
- How would you make this incremental so you do not rescan 300M rows nightly? _(Checks pipeline judgment: a per-user running sum plus count in a state table, updated from each day's new events.)_
- Two events share the same `event_timestamp` for one user. What does your query do? _(Tests awareness that ORDER BY `event_timestamp` alone is nondeterministic and needs a tiebreaker like `event_id`.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_space_between)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.