Drill 9 of 12 in Gaming questions
0

Interview Question Bank · LEAD · PARTITION BY · pairing events

9. Session length from start and end events

HardGamingQuestion #153

Concept: LEAD · PARTITION BY · pairing eventsshow

When start and end are separate event rows, pair each start with the event right after it for the same person: LEAD(event_at) and LEAD(event_type) OVER (PARTITION BY person ORDER BY event_at). Keep only the pairs where a start is followed by an end; a start followed by another start (or by nothing) never finished. Measure the gap with EXTRACT(EPOCH FROM interval) / 60, which counts the whole interval in minutes.

Example (different data):

WITH e AS (
  SELECT machine_id, kind, at,
    LEAD(kind) OVER w AS next_kind, LEAD(at) OVER w AS next_at
  FROM machine_events
  WINDOW w AS (PARTITION BY machine_id ORDER BY at)
)
SELECT machine_id, AVG(EXTRACT(EPOCH FROM next_at - at) / 60) AS avg_run_minutes
FROM e WHERE kind = 'power_on' AND next_kind = 'power_off'
GROUP BY machine_id;

Your task

Skyrift Arena's client logs a session_start when the game opens and a session_end when it closes. When the app crashes, the end never arrives. The analytics team wants each player's typical session length.

Pair each session_start with the same player's next event when that event is a session_end; a start followed by another start, or by nothing, was a crash and is skipped. Return username, sessions (completed sessions) and avg_minutes (their average length in minutes, rounded to 1 decimal). Sort by avg_minutes descending, then username.

Starting Postgres in your browser…

Checking your access…