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 · Hotjar
Analyze user journey through website funnel (landing → signup → trial → paid), calculate conversion rates by stage, and identify drop-off bottlenecks.
Hotjar's growth team needs to know where the website funnel leaks: the journey runs Landing → Signup → Trial-Start → Trial-Completed → Paid-Conversion, and somewhere between first visit and paid plan, most users vanish.
The analysis collapses raw events into one row per user (did they ever reach each stage?), then reports users at each stage plus step-to-step conversion rates — Landing→Signup, Signup→Trial, trial completion, trial→paid — and the end-to-end rate (paid ÷ landed). The stage with the steepest relative drop is the bottleneck, and it dictates whether the fix is onboarding, trial experience, or pricing.
Two subtleties separate strong answers. First, identity: a user landing twice from different sources must count once, with an explicit source-credit rule. Second, sample size: six users reveal the method, not the truth — the write-up should say what you'd check at six million events (instrumentation, stage definitions, source splits) rather than over-claiming.
User funnel events with user_id, event_type (Landing, Signup, Trial-Start, Trial-Completed, Paid-Conversion), event_date, campaign_source.
CREATE TABLE funnel_events (
event_id INTEGER PRIMARY KEY,
user_id INTEGER,
event_type TEXT,
event_date DATE,
campaign_source TEXT
);Your target
Analyze user journey through website funnel (landing → signup → trial → paid), calculate conversion rates by stage, and identify drop-off bottlenecks.
Loading the expected output…
WITH funnel_stages AS (
SELECT
user_id,
MAX(CASE WHEN event_type = 'Landing' THEN 1 ELSE 0 END) as landed,
MAX(CASE WHEN event_type = 'Signup' THEN 1 ELSE 0 END) as signed_up,
MAX(CASE WHEN event_type = 'Trial-Start' THEN 1 ELSE 0 END) as trial_started,
MAX(CASE WHEN event_type = 'Trial-Completed' THEN 1 ELSE 0 END) as trial_completed,
MAX(CASE WHEN event_type = 'Paid-Conversion' THEN 1 ELSE 0 END) as converted_paid
FROM funnel_events
GROUP BY user_id
),
stage_summary AS (
SELECT
COUNT(DISTINCT user_id) as total_landed,
COUNT(DISTINCT CASE WHEN signed_up = 1 THEN user_id END) as total_signups,
COUNT(DISTINCT CASE WHEN trial_started = 1 THEN user_id END) as total_trial_starts,
COUNT(DISTINCT CASE WHEN trial_completed = 1 THEN user_id END) as total_trial_completed,
COUNT(DISTINCT CASE WHEN converted_paid = 1 THEN user_id END) as total_paid_conversions
FROM funnel_stages
),
conversion_rates AS (
SELECT
total_landed,
total_signups,
total_trial_starts,
total_trial_completed,
total_paid_conversions,
ROUND(1.0 * total_signups / NULLIF(total_landed, 0) * 100, 2) as landing_to_signup_rate,
ROUND(1.0 * total_trial_starts / NULLIF(total_signups, 0) * 100, 2) as signup_to_trial_rate,
ROUND(1.0 * total_trial_completed / NULLIF(total_trial_starts, 0) * 100, 2) as trial_completion_rate,
ROUND(1.0 * total_paid_conversions / NULLIF(total_trial_completed, 0) * 100, 2) as trial_to_paid_rate,
ROUND(1.0 * total_paid_conversions / NULLIF(total_landed, 0) * 100, 2) as end_to_end_conversion_rate
FROM stage_summary
)
SELECT
'Landing' as funnel_stage,
total_landed as users_at_stage,
NULL as conversion_rate_pct,
total_landed as cumulative_users,
'Entry Point' as stage_description
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT
'Signup',
total_signups,
landing_to_signup_rate,
total_signups,
'Signup Form Completion'
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT
'Trial-Start',
total_trial_starts,
signup_to_trial_rate,
total_trial_starts,
'Activated Trial'
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT
'Trial-Completed',
total_trial_completed,
trial_completion_rate,
total_trial_completed,
'Finished 7-Day Trial'
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT
'Paid-Conversion',
total_paid_conversions,
trial_to_paid_rate,
total_paid_conversions,
'Subscribed to Paid Plan'
FROM (SELECT * FROM conversion_rates);
Step-by-step Solution:
Create funnel_stages CTE using conditional aggregation. For each user, mark 1 if they completed each funnel event (Landing, Signup, Trial-Start, Trial-Completed, Paid-Conversion) using MAX(CASE WHEN...). This creates a funnel path for each user.
Build stage_summary to count distinct users at each funnel stage. Count users where landed=1 (everyone), then count where signed_up=1, trial_started=1, etc. This shows drop-off at each step.
In conversion_rates CTE, calculate step-to-step conversion rates: landing_to_signup = (signups / landed) * 100, signup_to_trial = (trials / signups) * 100, etc. Also calculate end-to-end conversion (paid / landed).
Use UNION ALL to reshape data into a single result showing each funnel stage with users at that stage and conversion rate from previous stage. NULL for Landing stage since it's the entry point.
Order naturally through UNION to show funnel progression. This reveals bottlenecks: e.g., if landing_to_signup is 60% but signup_to_trial is 20%, the form needs fixing; if trial_to_paid is 10%, onboarding needs work.
Computing the reference output…
End-to-end conversion is 16.7% (1 paid customer from 6 landings) and the biggest leak is signup-to-trial — a third of signups never activate. Fix activation first (onboarding, trial-start nudges), then trial completion (50%), since the trial-to-paid close rate is comparatively healthy.
What the interviewer asks next
“Signup-to-trial drops from 100% to 66.7% — onboarding failure or low-intent signups? How do you tell from data?”
Strong answer covers: Segment the leak: split activation by campaign source (Organic vs Paid-Search vs Social) to separate intent quality from product friction before prescribing a fix.
“User 1006 lands twice from different sources, then converts — which source gets credit for the signup?”
Strong answer covers: Identity and dedupe: define one-user-one-journey rules (first vs last source, multi-touch) up front, because funnel math silently depends on the attribution choice.
“This is 6 users. Tomorrow it's 6 million events a day — what breaks in your approach?”
Strong answer covers: Scale thinking: event instrumentation and sessionization replace hand-traced funnels; stage definitions must be versioned, backfilled, and guarded against instrumentation drift.
“User 1006 lands twice — Organic first, Social second — then converts down the Social path. Which source owns that signup?”
Strong answer covers: Attribution inside funnels: first-touch credits discovery (Organic), last-touch credits conversion (Social) — the funnel total stays 1 either way, but channel ROI flips, so the rule must precede the report.
“Trial-to-paid reads 50% (1 of 2) — but 1001 converted a day after finishing trial while 1006 finished trial on Mar-14 with no conversion yet. Same thing?”
Strong answer covers: Censoring in funnels: 1006 may simply need more days — conversion rates need a fixed follow-up window (e.g. 14 days post-trial) or recent completers inflate the denominator unfairly.
“Paid-Search drove 2 signups (1002, 1005) and zero trials; Organic drove 3 landings and the only paid conversion. Verdict on paid spend?”
Strong answer covers: Source-quality read with humility: n=6 forbids budget verdicts, but the directional signal (paid signups stall at activation) tells you exactly what to validate at scale — signup-to-trial by source.
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.