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.

MediumTechnical

94 practiced

You are asked to attribute revenue to marketing channels. Given acquisition_events(user_id, channel, occurred_at) and purchases(user_id, amount, occurred_at), write two SQL approaches: (1) last-touch attribution, and (2) a time-decay attribution that gives more weight to more recent touches within 30 days prior to purchase. Provide SQL snippets and discuss trade-offs (bias, explainability, implementation complexity).

EasyTechnical

80 practiced

Explain the difference between INNER JOIN and LEFT JOIN in the context of identifying churned users. Given users(user_id, created_at) and events(user_id, occurred_at), write a SQL snippet to find users with no events in the last 30 days (i.e., potentially churned). Discuss null handling in your result.

EasyTechnical

91 practiced

Explain, in the context of growth analytics, the difference between 'retention' and 'churn'. Describe at least two SQL patterns you would use to calculate: (a) a simple retention rate for a cohort, and (b) a churn rate for a cohort over a 30-day window. Include a brief note about pitfalls (e.g., definition drift, time-window alignment).

MediumTechnical

84 practiced

Given an events table with event_name in (step1, step2, step3, step4), schema events(user_id INT, event_name TEXT, occurred_at TIMESTAMP, acquisition_channel TEXT). Write SQL that computes: for each acquisition_channel and cohort (signup week), the conversion rate from step1 -> step2 -> step3 -> step4, and the median time between consecutive steps per user. Ensure event ordering is respected and each step per user is the first occurrence of that step. Optimize for clarity and explain correctness.

EasyTechnical

68 practiced

Write a SQL query that returns, for the last 12 weeks, two metrics per week: weekly_new_users (users whose created_at falls in that week) and weekly_active_users (distinct user_id who had at least one event in that week). Use users and events tables and ensure weeks with zero values are present in the output.

Unlock Full Question Bank

Get access to hundreds of SQL for Growth Analytics interview questions and detailed answers.

Join thousands of developers preparing for their dream job.