The Space Between

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Meta

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.

The time between events is the tell.

Tables

event_data

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

Expected output

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

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
;

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`.)