Session Page View Distance

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

L5

Asked in

Google

Problem

A session's first and last page views are the ones with the lowest and highest view_id, and a session with only one view, or whose first or last view has no recorded dur_ms, has no distance to measure. Linking page_views to user_sessions through user_id, the UX research team wants the average across sessions of the last view's dur_ms minus the first view's dur_ms.

Page view distance per session.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

This is positional extraction dressed up as engagement research. The question is not how long the longest view ran; it is what dur_ms sat on the lowest and the highest view_id in each session. Anyone can join page_views to user_sessions. What separates candidates is refusing to answer with MAX(dur_ms) - MIN(dur_ms): those are the extremes of the values, not the values at the endpoints. Because aggregates skip NULLs, they quietly measure from the second view when the first one has no recorded duration, which invents a distance for a session the question says cannot be measured.

Getting to the answer

Step 1: Accept the grain the user_id link gives you

The only key between the tables is user_id, so every session inherits all of its user's views, and two sessions of the same user end up with identical endpoints. That is the stated linkage, not something to fix here. Say it out loud anyway: a user with five sessions counts five times in the average, and an interviewer is listening for whether you noticed.

Step 2: Number each session's views from both ends

Write two ROW_NUMBER() windows over PARTITION BY us.session_id, one ordered by pv.view_id ASC and one by DESC. The row with rn_asc = 1 is the first view and the row with rn_desc = 1 is the last, whatever their durations are. view_id is unique, so neither numbering has a tie to break.

Step 3: Count the views without collapsing them

COUNT(*) OVER (PARTITION BY us.session_id) stamps the session size on every row. The single-view guard then rides along with the endpoint rows instead of needing a second pass. Leave the count window unordered: it should cover the whole session.

Step 4: Pair the endpoints and subtract in order

Join page_ranked to itself on session_id, keeping the rn_asc = 1 row as f and the rn_desc = 1 row as l, and compute l.dur_ms - f.dur_ms. Leave NULLs alone. If either endpoint has no duration, the distance is NULL and AVG leaves that session out, which is exactly what the question asks.

WITH page_ranked AS (
  SELECT pv.user_id, us.session_id, pv.dur_ms, pv.view_id,
         ROW_NUMBER() OVER (PARTITION BY us.session_id ORDER BY pv.view_id ASC) AS rn_asc,
         ROW_NUMBER() OVER (PARTITION BY us.session_id ORDER BY pv.view_id DESC) AS rn_desc,
         COUNT(*) OVER (PARTITION BY us.session_id) AS view_count
  FROM page_views pv
  INNER JOIN user_sessions us ON pv.user_id = us.user_id
),
session_dist AS (
  SELECT f.session_id,
         l.dur_ms - f.dur_ms AS distance
  FROM page_ranked f
  INNER JOIN page_ranked l ON f.session_id = l.session_id
  WHERE f.rn_asc = 1 AND l.rn_desc = 1
  AND f.view_count > 1
)
SELECT AVG(distance) AS avg_distance FROM session_dist

Extremes of the values

MAX(dur_ms) - MIN(dur_ms) per session. It is never negative, and it skips NULLs, so a session whose first view has no duration is measured from its second view and counted.

Values at the endpoints

l.dur_ms - f.dur_ms on the rn_desc = 1 and rn_asc = 1 rows. It keeps its sign, and a missing endpoint makes the session unmeasurable, so it drops out of the average.

Common follow-up questions

• How would you limit each session to its own views using `session_start` and `session_duration_sec`? (Tests turning the fan-out join on `user_id` into a time-range join.)
• What changes if the team wants the absolute gap rather than last minus first? (Tests reading the sign of the metric as a business decision.)
• How would you weight the average per user instead of per session? (Tests noticing that the `user_id` link counts each user once per session.)