Loading…
Loading…
case studies · real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
real world impact
Solve free real-world SQL case studies from top companies — full schema, dataset, solution, and business insight. Great for interview prep.
Browse Case Studies Interview questions// SELECT A CASE STUDY
SQL case study · Spotify
Sessionize a raw clickstream event log using a 30-minute inactivity gap, then find the most common next action after each event type.
Spotify's product team stares at a raw clickstream (a timestamped log of every tap: app opens, plays, skips, pauses, likes, searches) and needs behavior, not events. Step one is sessionization: split each user's stream into sessions wherever the gap between consecutive events exceeds 30 minutes of inactivity — implemented with a LAG window (peeking at the previous row) to flag breaks, then a running total to number sessions.
Step two is the transition matrix: for every event, what's the next event in the same session (a LEAD window peeking forward)? Count each current→next pair and rank them. The dominant loop — play↔skip in both directions — reveals users auditioning tracks rather than enjoying them, which points the product fix at play-start relevance (autoplay, recommendations) instead of catalog size.
Close with method honesty: the 30-minute threshold is inherited, not proven — propose re-running at 15/60 minutes and checking whether conclusions survive. Thresholds you don't sensitivity-test are assumptions wearing lab coats.
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
);Your target
Sessionize a raw clickstream event log using a 30-minute inactivity gap, then find the most common next action after each event type.
Loading the expected output…
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 (julianday(event_timestamp) - julianday(prev_timestamp)) * 24 * 60 > 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;
Step-by-step Solution:
ordered_events uses LAG() to attach each event's previous event timestamp within the same user's event stream.
session_breaks flags a new session (is_new_session = 1) whenever there's no previous event or the gap since the previous event exceeds 30 minutes, using EXTRACT(EPOCH ...) to convert the timestamp difference into minutes.
sessions runs a running SUM() of is_new_session as a window function ordered by time, which produces a stable, incrementing session_num for every user — this is the standard gap-and-reset sessionization pattern.
next_event uses LEAD() partitioned by user_id and session_num to look ahead to the next event within the same session (never crossing a session boundary), pairing every event with whatever the user did right after it.
The final query counts how often each (current_event, next_event) pair occurs and ranks the transitions by frequency, surfacing the dominant behavioral pattern the product team can act on.
Computing the reference output…
Listening is a sample-and-skip loop: play-to-skip is the top transition (4) with skip-back-to-play right behind (3) — 7 of 19 transitions are users auditioning tracks. The biggest lever is relevance at play start (autoplay and recommendation precision), while app opens reliably convert into listening (play, browse, or search follow nearly every open).
What the interviewer asks next
“Play-to-skip dominates (4) with skip-back-to-play behind it (3) — content problem or discovery problem?”
Strong answer covers: Reading the loop: users auditioning then skipping points at relevance at play start (autoplay, recommendations) rather than catalog quality — fix what starts the play, not the library.
“Sessions split on a 30-minute inactivity gap — how would you validate that threshold instead of inheriting it?”
Strong answer covers: Sensitivity analysis: plot the gap distribution and re-run sessionization at 15/30/60 minutes — if conclusions survive, the threshold is safe; if they flip, say so.
“Nearly every app open converts into listening — what experiment lifts likes, the rarest event here?”
Strong answer covers: Experiment design from the funnel: likes follow sustained plays, so test reducing skip-rate (better first-track relevance) and measure like-rate per session as the north-star metric.
“Three users, 24 events, ~2 likes total — like-rate near 8%. Is the like button broken (friction), or are users content without it (satisfaction)?”
Strong answer covers: Distinguishing friction from contentment: correlate likes with session length and replays — long sessions without likes signal UI friction worth A/B testing, short ones signal satisfaction.
“pause_track appears exactly once (user 202) and always resolves back to play — should pauses count as engagement, exit intent, or be ignored?”
Strong answer covers: Semantic labeling of rare events: a pause-then-resume is an interrupted session, not a rejection — treating it like a skip corrupts the transition matrix, so define pause semantics explicitly.
“Three browse_playlist events produce one play (201), one bounce (202's lone evening browse), zero for 203 — is browse-to-play rate the discovery metric that matters?”
Strong answer covers: Funnel thinking on discovery: browse→play conversion isolates whether users find things worth playing — the 1-in-3 here is the baseline to beat with better playlist previews and ordering.
Questions test whether your SQL runs. Cases test whether you can own a decision. Learn this once, use it everywhere below.
Restate the grain
One output row = one what? Write it down first — every GROUP BY and join must serve that grain.
Explore before you aggregate
Eyeball every table first: NULL join keys, orphan keys, date coverage. Ten minutes here saves an hour of debugging.
Hand-trace one entity end to end
Compute one row's answer by hand, then demand your query agree. Trust the trace, not the query.
Build incrementally, validate each CTE
Run each CTE standalone and reconcile counts before stacking the next one.
Pressure-test boundaries and empties
Probe cutoffs, zero-activity entities, ties, and empty results — interviewers live on boundaries.
Recommend, don't just report
End with a decision, an owner, and what would change your mind. No recommendation, no case study.