Business Problem

Spotify's product analytics team suspects users are getting frustrated somewhere in the listening experience but the raw event log has no session boundaries — just a stream of timestamped events per user. They need the events grouped into sessions (a new session starts whenever a user is inactive for more than 30 minutes), and then, for every event type, the single most common next action a user takes immediately after it, so the product team can see patterns like 'users skip right after playing a track' and prioritize fixing recommendation relevance over other UX issues. Raw clickstream and event-log data almost never comes pre-segmented into sessions, so sessionization — grouping a user's events based on a gap-based inactivity threshold — is a foundational technique in product and behavioral analytics. Once sessions are defined, analyzing what action typically follows another (a 'next-event' or bigram transition analysis) reveals friction points and usage patterns that a simple event-count report would completely miss.

Dataset & Schema

A single user_events table with event_id, user_id, event_type (e.g. app_open, play_track, skip_track, pause_track, like_track, search, browse_playlist), and event_timestamp.

CREATE TABLE user_events (
  event_id INTEGER PRIMARY KEY,
  user_id INTEGER,
  event_type TEXT,
  event_timestamp TIMESTAMP
);

SQL Solution

WITH ordered_events AS (
  SELECT
    user_id,
    event_type,
    event_timestamp,
    LAG(event_timestamp) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS prev_timestamp
  FROM user_events
),
session_breaks AS (
  SELECT
    *,
    CASE
      WHEN prev_timestamp IS NULL THEN 1
      WHEN EXTRACT(EPOCH FROM (event_timestamp - prev_timestamp)) / 60.0 > 30 THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered_events
),
sessions AS (
  SELECT
    *,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_num
  FROM session_breaks
),
next_event AS (
  SELECT
    user_id,
    session_num,
    event_type AS current_event,
    LEAD(event_type) OVER (PARTITION BY user_id, session_num ORDER BY event_timestamp) AS next_event
  FROM sessions
),
transitions AS (
  SELECT
    current_event,
    next_event,
    COUNT(*) AS transition_count
  FROM next_event
  WHERE next_event IS NOT NULL
  GROUP BY current_event, next_event
)
SELECT
  current_event,
  next_event,
  transition_count,
  RANK() OVER (ORDER BY transition_count DESC) AS popularity_rank
FROM transitions
ORDER BY transition_count DESC, current_event;

Explanation