Spotify Data Analyst Case Interview — Product Analytics, Experimentation, and SQL Readout

This live case mirrors how Spotify evaluates data analysts in squads within its tribes model. It focuses on end to end product analytics for both Premium and Ad Supported listeners, experiment design and interpretation, SQL level reasoning using BigQuery style thinking, and clear storytelling to cross functional partners. What the case covers - Problem framing tied to a real Spotify surface such as AI DJ, Daylist, or Blend. You will define success for a new or iterated feature that touches both Free and Premium funnels and spans music and podcasts. - Metrics and North Star alignment. Expect to identify primary metrics like engagement time, skips per hour, save rate, playlist adds, podcast completion, conversion to Premium, and retention, plus ad side metrics such as ad impressions, fill rate, eCPM, and ad load. You should propose guardrails for listener experience, creator outcomes, and platform health (for example latency, crash rate, and content diversity). - Experiment design under real constraints. You will choose unit of randomization, outline eligibility rules, power and duration tradeoffs, and discuss novelty effects, regional rollouts, and how to avoid cross contamination across devices. Expect to balance short term engagement lifts with medium term retention and premium conversion, reflecting Spotify’s think it, build it, ship it, tweak it culture. - SQL and data reasoning. Without needing to write perfect code, you will sketch how you would query core event logs to compute listener level metrics and experiment readouts. Typical tables to reason about include users, subscriptions, sessions, track_plays, podcast_plays, ads_impressions, and ab_assignments. You should describe joins, filters, and aggregation levels, plus handling of late arriving events, bots, and duplicated plays. - Readout and stakeholder communication. You will be given a small, simplified A B test summary and asked to interpret effects by segment (Free vs Premium), market, and device. You will call out tradeoffs, recommend rollout or iteration, and outline next steps for the squad, including creator impact checks and privacy by design considerations for EU markets. Case storyline example - Context. Your squad is evaluating an enhancement to AI DJ that mixes short podcast highlights between songs for Free users in EMEA to increase session depth and Premium conversion. - Task A. Define success and propose the metric framework. Identify primary, secondary, and guardrail metrics, including any leading indicators and long term counters to monitor post launch. - Task B. Design the experiment. Specify treatment and control, randomization key, sample sizing approach, test length, and pre registration of metrics. Discuss bias risks such as time of day and power user over representation and how you would mitigate them. - Task C. Analytics plan. Outline the SQL you would use to compute per user per day metrics like minutes played, skips, save rate, podcast completion, ad load, and Premium trial start, including key filters and windowing. Describe how you would slice results by cohort and market. - Task D. Readout. Interpret a provided summary table with deltas and confidence intervals, call out any metric movement in opposite directions across segments, and make a recommendation with explicit tradeoffs. Evaluation rubric aligned to Spotify - Product sense and listener creator empathy. Do your metrics capture value for both sides of the marketplace and account for Free vs Premium experiences and ads economics. - Experimentation depth. Sound choices on unit, power, duration, guardrails, and novelty effects, plus an understanding of sequential looks and false discovery. - SQL and data rigor. Clear reasoning about joins, grain, deduplication, and edge cases in event data; pragmatic BigQuery oriented approach. - Communication and storytelling. Crisp structuring, visualizable narrative for PMs and engineers, and clear next steps that fit the squad model. - Culture fit. Evidence of iterative mindset, autonomy with alignment, and respect for privacy and regional constraints. Logistics you can expect - Format. 60 minute live case with a product analyst or data scientist. 5 minutes for context and clarifying questions, 25 minutes for metrics and experiment design, 15 minutes for SQL and data reasoning, 10 minutes for readout and recommendation, 5 minutes for Q and A. - Materials. Digital whiteboard or doc for sketching tables and queries. No external tools required. What great looks like - Frames the problem with explicit assumptions, separates listener, creator, and business metrics, proposes robust guardrails, anticipates Free to Premium funnel dynamics and ads revenue tradeoffs, and delivers a decision oriented readout with a measured rollout plan and follow up analyses.