The Growth Analyst SQL Interview — Cohorts, Funnels, Retention

Growth is where SQL gets analytical. A generic data analyst loop tests whether you can write clean queries. A growth analyst loop tests whether you can answer questions like "why did activation drop on the iOS cohort that signed up via paid social in the last two weeks" in a single query, with a confidence interval, while explaining your assumptions out loud.

Growth interviewers are looking for a specific kind of analyst: someone who can move comfortably between the SQL and the why. The SQL is rarely the bottleneck. The reasoning is. This guide walks through the three question patterns that show up at every growth analyst loop in 2026 — cohort retention, funnel conversion, and channel attribution — and how to nail each.

Why Growth Is Different

Three things distinguish growth analyst interviews from generic data analyst loops.

First, the questions are about user behavior over time, not snapshots. You're rarely asked "how many users signed up last month." You're asked "how does activation differ between cohorts signed up before and after the new onboarding flow shipped." That requires cohort thinking, not single-table reporting.

Second, business framing is half the score. The interviewer wants to see that you can translate "churn is up" into "churn is up — but is it new-user churn or established-user churn? Free or paid? Mobile or desktop?" Growth interviewers care intensely about whether you'd dig in the right direction.

Third, the math gets real. Conversion rates, retention curves, LTV — these have non-trivial denominators and edge cases. Junior candidates write queries that compute the right thing but with hidden bugs (counting cancelled trials as active, double-counting users who signed up twice). Senior candidates spot those bugs unprompted.

The Three Pattern Types

Pattern 1: Cohort Retention

The flagship growth question. "Group users by when they signed up, then track how many were still active in subsequent periods."

Example: "For each monthly signup cohort, what percentage of users were still active in months 1, 3, and 6?"

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM users
  GROUP BY user_id
),
user_activity AS (
  SELECT
    c.user_id,
    c.cohort_month,
    DATE_TRUNC('month', e.event_date) AS active_month
  FROM cohorts c
  JOIN events e ON e.user_id = c.user_id
  WHERE e.event_name = 'session_start'
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size,
  COUNT(DISTINCT CASE
    WHEN active_month = cohort_month + INTERVAL '1 month'
    THEN user_id
  END)::numeric / NULLIF(COUNT(DISTINCT user_id), 0) AS m1_retention,
  COUNT(DISTINCT CASE
    WHEN active_month = cohort_month + INTERVAL '3 months'
    THEN user_id
  END)::numeric / NULLIF(COUNT(DISTINCT user_id), 0) AS m3_retention,
  COUNT(DISTINCT CASE
    WHEN active_month = cohort_month + INTERVAL '6 months'
    THEN user_id
  END)::numeric / NULLIF(COUNT(DISTINCT user_id), 0) AS m6_retention
FROM user_activity
GROUP BY cohort_month
ORDER BY cohort_month;

What's being tested: can you define cohorts, join activity, compute retention at multiple horizons.

The senior moves:

• DISTINCT user_id inside COUNT — same user can have multiple events per month
• NULLIF to guard division by zero
• Cast to numeric before dividing — integer division will silently truncate to 0
• Acknowledge that recent cohorts won't have month-6 data yet (the "triangle problem")
• Define what "active" means out loud — does it require a session start, a purchase, or just any event?

The biggest senior signal here: noticing that this retention definition counts any activity in the target month. If you wanted return retention (came back specifically after being gone), you'd need a different query. Mentioning that distinction unprompted earns major credit.

Pattern 2: Funnel Conversion

"Multi-step user journey from one event to another. Compute conversion rates between steps."

Example: "For the signup funnel — landing_view, signup_start, signup_complete, activation_complete — compute the conversion rate at each step and identify the biggest drop-off."

WITH per_user_funnel AS (
  SELECT
    user_id,
    MIN(CASE WHEN event = 'landing_view'        THEN event_time END) AS step1_time,
    MIN(CASE WHEN event = 'signup_start'        THEN event_time END) AS step2_time,
    MIN(CASE WHEN event = 'signup_complete'     THEN event_time END) AS step3_time,
    MIN(CASE WHEN event = 'activation_complete' THEN event_time END) AS step4_time
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
),
sequential AS (
  SELECT
    COUNT(*) FILTER (WHERE step1_time IS NOT NULL)                                                                  AS s1_landed,
    COUNT(*) FILTER (WHERE step1_time IS NOT NULL AND step2_time IS NOT NULL AND step2_time >= step1_time)         AS s2_started,
    COUNT(*) FILTER (WHERE step2_time IS NOT NULL AND step3_time IS NOT NULL AND step3_time >= step2_time)         AS s3_signed_up,
    COUNT(*) FILTER (WHERE step3_time IS NOT NULL AND step4_time IS NOT NULL AND step4_time >= step3_time)         AS s4_activated
  FROM per_user_funnel
)
SELECT
  s1_landed,
  s2_started,
  s3_signed_up,
  s4_activated,
  ROUND(s2_started::numeric    / NULLIF(s1_landed, 0)    * 100, 1) AS land_to_start,
  ROUND(s3_signed_up::numeric  / NULLIF(s2_started, 0)   * 100, 1) AS start_to_signup,
  ROUND(s4_activated::numeric  / NULLIF(s3_signed_up, 0) * 100, 1) AS signup_to_activate
FROM sequential;

What's being tested: can you build a sequential funnel (steps must happen in order), not just an "any event" funnel.

The senior moves:

• Use MIN() to capture the first occurrence of each event
• Enforce sequential ordering (step2_time >= step1_time)
• Use FILTER clause for conditional aggregation (cleaner than CASE WHEN ... THEN 1 ELSE 0)
• After computing rates, point at the biggest drop-off and offer a hypothesis: "signup_to_activate is the lowest at 32%. I'd want to look at what activation requires — is it a verification email, a profile completion?"

That last move — interpreting the result and proposing where to dig — is the difference between a competent answer and a senior one.

Pattern 3: Channel Attribution

"Which acquisition channels are driving the best users — not just the most users."

Example: "For users who signed up in the last 90 days, compute the LTV per signup by acquisition channel."

WITH cohort AS (
  SELECT
    user_id,
    channel,
    created_at
  FROM users
  WHERE created_at >= CURRENT_DATE - INTERVAL '90 days'
),
revenue AS (
  SELECT
    user_id,
    SUM(amount) AS total_revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
)
SELECT
  c.channel,
  COUNT(DISTINCT c.user_id)                                              AS signups,
  COALESCE(SUM(r.total_revenue), 0)                                      AS total_revenue,
  ROUND(COALESCE(SUM(r.total_revenue), 0) / NULLIF(COUNT(DISTINCT c.user_id), 0), 2) AS revenue_per_signup
FROM cohort c
LEFT JOIN revenue r ON r.user_id = c.user_id
GROUP BY c.channel
ORDER BY revenue_per_signup DESC;

What's being tested: can you measure channel quality, not just channel volume.

The senior moves:

• LEFT JOIN (not INNER JOIN) — most signups generate $0 of revenue, and you want them in the denominator
• COALESCE(SUM(...), 0) so channels with zero revenue don't show NULL
• Acknowledge that 90 days isn't long enough to see true LTV — these are early indicators of channel quality
• Call out that you'd also want to look at cost per acquisition by channel to compute true ROI

The interpretation move: "Paid social has the highest revenue_per_signup, but if it also has the highest CPA, the net economics could still be worse than organic. I'd want to bring in the spend data before drawing conclusions."

That two-sided thinking — "this metric is high, but here's what could make it misleading" — is the move that separates senior growth analysts from mid-level.

The Three Mistakes Growth Candidates Make

1. Treating activity as a single binary. A user who logged in but didn't do anything meaningful is different from a user who completed a purchase. Growth interviewers will probe this. Define "active" with specificity.

2. Forgetting the triangle problem. Recent cohorts haven't aged enough to show month-6 retention. If you display month-6 retention for cohorts younger than 6 months, those cells will show 0% or NULL and the chart will mislead. Acknowledge this constraint.

3. Mixing cohorts and snapshots. A common error: computing "month-3 retention" by looking at users active in month 3 of every cohort, but using the same denominator. Each cohort needs its own denominator (the size of that cohort, not the total user base).

What To Practice

Growth analyst loops are domain-specific. Practice on schemas that look like real growth data — users, sessions, events, subscriptions, channels — not on toy schemas.

Run yourself through five problems in each pattern (cohort, funnel, attribution). For each problem, force yourself to:

1. Define the metric out loud in business terms before writing SQL
2. Write the query
3. Interpret the result and propose what you'd dig into next

Three full passes through 15 problems builds more growth-analyst-ready muscle than 100 generic SQL drills. The pattern recognition is the skill — once you see a problem as a Pattern 1 (cohort), Pattern 2 (funnel), or Pattern 3 (attribution), the SQL skeleton comes automatically.

The candidates who land growth roles aren't the ones with the fastest SQL. They're the ones who instinctively reach for the right pattern, write it cleanly, and tell a story about what the result means.