Fastest Page View to Click

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

Meta

Problem

Return the three quickest browse-to-click transitions in event_data, from the shortest gap to the longest with a tied gap going to the earlier browsing event, each with that event's type and user_id, both timestamps, the gap in seconds, and the average gap across every transition in the stream. A transition is a 'page_view', 'search' or 'open' event whose very next event in the whole stream, from any user and of any type, is a 'button_click'.

How fast from view to click?

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 really is

This is a one-step lookahead over a shared stream, dressed up as engagement analytics. Everyone reaches for LEAD. What separates candidates is deciding what LEAD is allowed to see. 'Next' means the literal next event, any type, any user. Filter the stream down to browse and click events first and a click that came after a purchase suddenly pairs with an earlier search. Skip the browse-type check and every login or error sitting before a click joins the podium. Either way the three rows and the average describe the wrong events.

Building it

Step 1: Look ahead over the whole stream

In event_stream, take two LEAD lookups over the same ORDER BY event_timestamp: one for next_type, one for next_ts. No WHERE here and no PARTITION BY user_id: the stream is shared, and an intervening purchase has to stay visible so it can block a pair.

Step 2: Keep browse events that a click follows

In transitions, keep rows whose event_type is one of the three browsing types and whose next_type is 'button_click'. The user_id carried along is the browser's, which is what the output asks for.

Step 3: Turn the difference into seconds

(JULIANDAY(next_ts) - JULIANDAY(event_timestamp)) * 86400 gives elapsed seconds and survives midnight and year boundaries. Forget the * 86400 and a two-second gap reads as a tiny fraction of a day.

Step 4: Average the whole set, then cut to three

(SELECT AVG(gap_seconds) FROM transitions) reads the full CTE, so it ignores the LIMIT 3 on the outer query. Sort by gap_seconds ascending, let the earlier browse_time win a tied gap, and keep three rows.

WITH event_stream AS (
  SELECT user_id,
    event_type,
    event_timestamp,
    LEAD(event_type) OVER (ORDER BY event_timestamp) AS next_type,
    LEAD(event_timestamp) OVER (ORDER BY event_timestamp) AS next_ts
  FROM event_data
),
transitions AS (
  SELECT user_id,
    event_type AS browse_event,
    event_timestamp AS browse_time,
    next_ts AS click_time,
    (JULIANDAY(next_ts) - JULIANDAY(event_timestamp)) * 86400 AS gap_seconds
  FROM event_stream
  WHERE event_type IN ('page_view', 'search', 'open')
    AND next_type = 'button_click'
)
SELECT user_id,
  browse_event,
  browse_time,
  click_time,
  gap_seconds,
  (SELECT AVG(gap_seconds) FROM transitions) AS avg_gap_seconds
FROM transitions
ORDER BY gap_seconds ASC, browse_time ASC
LIMIT 3

Filter the stream first

Putting the IN list inside event_stream hides purchases and logins from LEAD. A search, then a purchase, then a click becomes a transition it never was, and its gap lands in the average.

Filter the starting row

LEAD sees every event, so an intervening purchase blocks the pair. The IN list then only decides which rows may start a transition.

Common follow-up questions

• Product decides a transition only counts when the browser and the clicker are the same user. What changes? (Adding `PARTITION BY user_id` to both windows, and noticing the average shifts because pairs change.)
• A click five days after a search is technically next. How would you exclude stale pairs? (A max-gap filter on `gap_seconds` and whether it belongs before or after the average.)
• Report the median gap per browsing type instead of one overall average. How does the benchmark change? (Grouping `transitions` by `browse_event` and picking the middle value per group.)