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…