SQL for Growth Analytics Questions
Domain specific SQL patterns used in growth analytics: cohort analysis, retention and churn calculations, funnel analysis for multi step user journeys, acquisition cohort queries, lifetime value computations, customer segmentation, and event aggregation for time series. Emphasis on USING GROUP BY, window functions, CTEs, date bucketing, cohort windows, and efficient joins against event tables to compute retention curves, conversion rates, and growth metrics at scale.
HardTechnical
0 practiced
Implement, using SQL (recursive CTE allowed), a transformation that computes for each user the Nth active week index since signup (i.e., week_index where week_index = 0 for signup week, 1 for the next week with activity, etc.) up to 52 weeks. Use events(user_id, occurred_at) and users(user_id, signup_ts). Provide the recursive CTE and explain termination and performance considerations.
MediumTechnical
0 practiced
Write SQL that computes a 7-day rolling conversion rate from 'trial_start' to 'pay' events per country, where rolling is computed by calendar day (i.e., for each day D compute conversions among trials that started in [D-6, D]). Use events(user_id, event_name, occurred_at, country). Explain how to handle users who change country between trial_start and pay.
HardSystem Design
0 practiced
Design an incremental ETL SQL pattern to maintain a daily pre-aggregated cohort table used by dashboards. Requirements: idempotent runs, handle late-arriving events up to 7 days, and ensure correctness if a historical row is reprocessed. Provide pseudo-SQL for MERGE/UPSERT logic and explain how you schedule/partition updates to minimize compute and avoid duplication.
HardTechnical
0 practiced
Produce a retention pivot (day 0..30) without using an explicit cross join between cohorts and days. Use a numbers table (or generate_series) and lateral joins (or equivalent) to only produce rows that have potential matches. Show the SQL pattern and explain why it reduces data blowup compared to cohort x day cross join. Use users and events tables in your example.
EasyTechnical
0 practiced
Given purchases(purchase_id INT, user_id INT, amount DECIMAL, occurred_at TIMESTAMP) and users(user_id INT, created_at TIMESTAMP, acquisition_channel TEXT), write a SQL query to compute 30-day LTV per acquisition_channel: average cumulative revenue per user in the 30 days after first event. Return acquisition_channel, cohort_size, avg_ltv_30d. State assumptions about refunds and attribution.
Unlock Full Question Bank
Get access to hundreds of SQL for Growth Analytics interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.