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.

HardSystem Design

65 practiced

Design an incremental SQL pipeline that updates a daily retention matrix (cohort_week x day_number) without recomputing all history. Describe the schema of intermediate tables/materialized views, the incremental SQL logic (upserts or merges), and how you would handle late-arriving / backfilled events.

Sample Answer

Requirements & constraints:- Update a daily retention matrix keyed by cohort_week (user signup week) and day_number (0 = signup day).- Must be incremental (no full-history recompute), support late-arriving/backfill, be idempotent.

High-level components:- events_raw (append-only)- events_staging (daily aggregated per user/day)- cohorts (user_id → cohort_week)- retention_daily (materialized table: cohort_week, day_number, active_users, cohort_size, last_updated)

Schemas (examples):events_raw(event_id, user_id, event_time, event_type, payload, ingested_at)events_staging(user_id, event_date, had_activity boolean, last_event_time)cohorts(user_id, cohort_week date)retention_daily(cohort_week date, day_number int, active_users bigint, cohort_size bigint, last_updated timestamp)

Incremental flow:1) Ingest raw events partitioned by ingested_at. Deduplicate by event_id.2) Daily batch to produce events_staging for days that changed (partition prune). Aggregate:

sql

INSERT INTO events_staging (user_id,event_date,had_activity,last_event_time)
SELECT user_id, DATE(event_time) AS event_date, TRUE, MAX(event_time)
FROM events_raw
WHERE DATE(event_time) BETWEEN :start AND :end
GROUP BY user_id, DATE(event_time)
ON CONFLICT (user_id,event_date) DO UPDATE
SET had_activity = TRUE, last_event_time = EXCLUDED.last_event_time
WHERE events_staging.last_event_time < EXCLUDED.last_event_time;

3) Join staging to cohorts to compute per-cohort/day aggregates and upsert into retention_daily using MERGE/UPSERT:

sql

MERGE INTO retention_daily r
USING (
  SELECT c.cohort_week,
         DATE_DIFF(es.event_date, c.cohort_week, DAY) AS day_number,
         COUNT(DISTINCT es.user_id) AS active_users_new
  FROM events_staging es
  JOIN cohorts c USING (user_id)
  WHERE es.event_date BETWEEN c.cohort_week AND DATE_ADD(c.cohort_week, INTERVAL :max_day DAY)
  GROUP BY 1,2
) src
ON r.cohort_week = src.cohort_week AND r.day_number = src.day_number
WHEN MATCHED THEN UPDATE SET active_users = src.active_users_new, last_updated = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (cohort_week, day_number, active_users, cohort_size, last_updated)
VALUES (src.cohort_week, src.day_number, src.active_users_new,
        (SELECT COUNT(*) FROM cohorts WHERE cohort_week = src.cohort_week),
        CURRENT_TIMESTAMP);

Cohort size maintenance:- Maintain cohort_size in retention_daily or a separate cohorts_summary table updated when cohorts change (e.g., user signups). Use idempotent incremental updates.

Handling late-arriving / backfilled events:- Maintain a watermark (e.g., data_lag_days = 14). For any event with event_time < CURRENT_DATE - watermark, route to a backfill pipeline: - Recompute events_staging for affected event_date(s) and re-run the MERGE for only the impacted cohort_weeks/day_numbers.- Track provenance: retention_daily.last_updated and a processed_event_max_ingested_at per (cohort_week, day_number). If incoming events have ingested_at <= stored watermark skip; if newer, reprocess that partition.- For large historical backfills, run targeted re-aggregation jobs (parallel by cohort_week) and mark reprocessed windows.

Reliability & best practices:- Partition tables by date/week to prune scans.- Use deterministic deduplication keys for idempotency.- Keep retention_daily updates as single-statement MERGEs for atomicity.- Monitor row counts and delta sizes; if deltas grow large, consider incremental materialized view refreshes or streaming upserts.- Document SLA: watermark window, expected consistency, and backfill procedures.

MediumTechnical

82 practiced

Write SQL to compute the lifetime conversion funnel where users can convert multiple times, but we only care about the first conversion per user. Given events(user_id, event_name, occurred_at) where event_name='signup' and 'purchase', compute, per signup_week, the fraction of users who made their first purchase within 0,7,30 days. Use PostgreSQL.

Sample Answer

Approach: compute each user’s signup_date (first signup), their first_purchase_date after that signup, bin the time delta into 0/7/30-day windows, then aggregate by signup_week to produce fractions.

sql

WITH first_events AS (
  -- 1) For each user, get first signup and first purchase on/after that signup
  SELECT
    u.user_id,
    MIN(CASE WHEN event_name = 'signup' THEN occurred_at END) AS signup_at,
    MIN(CASE WHEN event_name = 'purchase' THEN occurred_at END) FILTER (
      WHERE event_name = 'purchase'
    ) AS any_purchase_at
  FROM events u
  GROUP BY u.user_id
),
first_purchase_after_signup AS (
  -- 2) compute first purchase that occurs >= signup_at (ignore purchases before signup)
  SELECT
    e.user_id,
    f.signup_at,
    MIN(e.occurred_at) AS first_purchase_at
  FROM events e
  JOIN first_events f ON e.user_id = f.user_id
  WHERE e.event_name = 'purchase'
    AND f.signup_at IS NOT NULL
    AND e.occurred_at >= f.signup_at
  GROUP BY e.user_id, f.signup_at
),
users_cohort AS (
  -- 3) cohort: every user who signed up, with their first purchase datetime (nullable)
  SELECT
    f.user_id,
    DATE_TRUNC('week', f.signup_at)::date AS signup_week,
    f.signup_at,
    p.first_purchase_at,
    -- days between signup and first purchase (integer days)
    CASE WHEN p.first_purchase_at IS NOT NULL
         THEN (p.first_purchase_at::date - f.signup_at::date)
         ELSE NULL END AS days_to_first_purchase
  FROM first_events f
  LEFT JOIN first_purchase_after_signup p USING (user_id, signup_at)
  WHERE f.signup_at IS NOT NULL
)
-- 4) aggregate per signup_week
SELECT
  signup_week,
  COUNT(*) AS users_signed_up,
  -- fraction with first purchase same day (0 days)
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_first_purchase = 0) / NULLIF(COUNT(*),0), 2) AS pct_first_purchase_0d,
  -- fraction within 7 days (0..7)
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_first_purchase IS NOT NULL AND days_to_first_purchase <= 7) / NULLIF(COUNT(*),0), 2) AS pct_first_purchase_7d,
  -- fraction within 30 days (0..30)
  ROUND(100.0 * COUNT(*) FILTER (WHERE days_to_first_purchase IS NOT NULL AND days_to_first_purchase <= 30) / NULLIF(COUNT(*),0), 2) AS pct_first_purchase_30d
FROM users_cohort
GROUP BY signup_week
ORDER BY signup_week;

Key points:- We use the user’s first signup as cohort anchor. Purchases before signup are ignored.- days_to_first_purchase uses date subtraction to count full calendar days; adjust to use timestamps if you need precise hours.- NULLIF avoids divide-by-zero; percentages returned as percent with 2 decimals.

Edge cases:- Users with no purchase => days_to_first_purchase is NULL (counts as non-converters).- Multiple signups: using MIN(signup_at) picks first signup; change if you need cohort by later signups.- Timezones: ensure occurred_at is normalized (UTC) or convert before truncation.

Alternative:- Use window functions to compute first events per user (ROW_NUMBER() PARTITION BY user_id ORDER BY occurred_at) and then join signup/purchase rows — equivalent but may be clearer for some readers.

MediumTechnical

64 practiced

Write SQL to compute a conversion funnel breakdown per day that also includes time to convert median for users who converted that day.Schema:

events(user_id, event_name, occurred_at)

For each day, compute: visits, signups, purchases, conversion_rate (signups/visits), median_time_to_purchase_seconds for users who purchased within 14 days of signup on that day. Provide PostgreSQL SQL using percentile_cont for median.

Sample Answer

Approach: derive per-day counts of visits, signups, purchases (based on event date), then compute, for users who signed up on a given day, the time in seconds to their first purchase within 14 days of that signup. Use percentile_cont(0.5) to get median_time_to_purchase_seconds. Aggregate per day.

sql

WITH
-- isolate events with timestamps and event day
e AS (
  SELECT
    user_id,
    event_name,
    occurred_at,
    (occurred_at::date) AS event_date
  FROM events
),

-- daily counts of each event type (distinct users to avoid double-counting per user per day)
daily_counts AS (
  SELECT
    event_date,
    COUNT(DISTINCT CASE WHEN event_name = 'visit' THEN user_id END) AS visits,
    COUNT(DISTINCT CASE WHEN event_name = 'signup' THEN user_id END) AS signups,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchases
  FROM e
  WHERE event_name IN ('visit','signup','purchase')
  GROUP BY event_date
),

-- get each user's signup time (first signup) and the signup_date (cohort day)
user_signup AS (
  SELECT
    user_id,
    MIN(occurred_at) AS signup_at,
    MIN(occurred_at)::date AS signup_date
  FROM e
  WHERE event_name = 'signup'
  GROUP BY user_id
),

-- find each user's first purchase after signup (if any) and ensure it's within 14 days
first_purchase_after_signup AS (
  SELECT
    s.user_id,
    s.signup_at,
    s.signup_date,
    MIN(p.occurred_at) AS first_purchase_at
  FROM user_signup s
  JOIN e p
    ON p.user_id = s.user_id
   AND p.event_name = 'purchase'
   AND p.occurred_at >= s.signup_at
   AND p.occurred_at <= s.signup_at + INTERVAL '14 days'
  GROUP BY s.user_id, s.signup_at, s.signup_date
),

-- compute time-to-purchase in seconds for users who converted within 14 days
ttc AS (
  SELECT
    signup_date AS event_date,
    EXTRACT(EPOCH FROM (first_purchase_at - signup_at))::BIGINT AS seconds_to_purchase
  FROM first_purchase_after_signup
)

-- final aggregation: left join daily counts and median time-to-purchase
SELECT
  d.event_date,
  COALESCE(d.visits, 0) AS visits,
  COALESCE(d.signups, 0) AS signups,
  COALESCE(d.purchases, 0) AS purchases,
  CASE WHEN COALESCE(d.visits,0) = 0 THEN 0.0
       ELSE ROUND( (COALESCE(d.signups,0)::numeric / d.visits) , 4)
  END AS conversion_rate,
  -- median in seconds using percentile_cont over aggregated per-day ttc values
  CASE WHEN COUNT(t.seconds_to_purchase) = 0 THEN NULL
       ELSE (percentile_cont(0.5) WITHIN GROUP (ORDER BY t.seconds_to_purchase))::BIGINT
  END AS median_time_to_purchase_seconds
FROM
  -- ensure we cover days present in any counts or signups
  (
    SELECT event_date FROM daily_counts
    UNION
    SELECT signup_date FROM user_signup
  ) days
  LEFT JOIN daily_counts d ON d.event_date = days.event_date
  LEFT JOIN ttc t ON t.event_date = days.event_date
GROUP BY d.event_date, d.visits, d.signups, d.purchases
ORDER BY d.event_date;

Key points:- Counts use DISTINCT user_id per day to avoid inflating counts if a user has multiple events same day.- Median computed only from users who signed up that day and purchased within 14 days.- conversion_rate is signups/visits; handle zero visits to avoid division by zero.Edge cases:- Users with multiple signups: using first signup as cohort anchor.- Purchases before signup are excluded.- If no converters on a day, median returns NULL.Alternatives:- Use window functions to compute first events per user if you want earliest visit/signup across entire history.- For large tables, pre-filter and index on (event_name, occurred_at) and consider materialized daily aggregates for performance.

EasyTechnical

76 practiced

A product manager asks for 30-day LTV per acquisition cohort.Tables:

users(user_id BIGINT, signup_date DATE, utm_campaign TEXT)
purchases(user_id BIGINT, amount NUMERIC, purchased_at TIMESTAMP)

Write SQL to compute, for each signup_week, the average revenue per user (ARPU) within their first 30 days. Return: signup_week, users, total_revenue_30d, arpu_30d.Use PostgreSQL; assume timezone UTC.

Sample Answer

Approach: compute each user’s revenue within 30 days of their signup (inclusive of signup moment), then aggregate by cohort week (week starting Monday via date_trunc('week')). This uses a per-user aggregation first to avoid double-counting users with zero purchases.

sql

-- Per-user 30-day revenue, then cohort aggregation
WITH user_revenue_30d AS (
  SELECT
    u.user_id,
    date_trunc('week', u.signup_date::timestamptz AT TIME ZONE 'UTC')::date AS signup_week,
    COALESCE(SUM(p.amount), 0)::numeric AS revenue_30d
  FROM users u
  LEFT JOIN purchases p
    ON p.user_id = u.user_id
    -- purchases in [signup_date, signup_date + 30 days)
    AND p.purchased_at >= (u.signup_date::timestamptz AT TIME ZONE 'UTC')
    AND p.purchased_at <  (u.signup_date::timestamptz AT TIME ZONE 'UTC') + INTERVAL '30 days'
  GROUP BY u.user_id, signup_week
)

SELECT
  signup_week,
  COUNT(*) AS users,
  SUM(revenue_30d) AS total_revenue_30d,
  CASE WHEN COUNT(*) = 0 THEN 0
       ELSE ROUND(SUM(revenue_30d)::numeric / COUNT(*)::numeric, 2)
  END AS arpu_30d
FROM user_revenue_30d
GROUP BY signup_week
ORDER BY signup_week;

Key points:- Use a LEFT JOIN so users with no purchases contribute zero revenue.- date_trunc('week', ...) produces cohort by week; casting to UTC ensures consistency.- We aggregate per user first to avoid inflating averages when a user has multiple purchases.Edge cases:- Users with signup_time precision beyond date — code treats signup_date as midnight UTC; if signup timestamps exist use signup_timestamp instead.- Purchases exactly at signup are included; purchases at signup +30 days are excluded (30-day window is half-open).Alternative: compute per-purchase join then use COUNT(DISTINCT user_id) for ARPU, but per-user aggregation is clearer and avoids DISTINCT overhead.

HardTechnical

92 practiced

Write SQL to compute cohort retention using array_agg to list active users per day for a cohort week. Schema:

users(user_id, signup_date)
events(user_id, occurred_at)

Produce: cohort_week, day_number, active_user_ids (array). Discuss memory and performance implications and when this approach is appropriate.

Sample Answer

Approach: compute each user’s cohort_week = date_trunc('week', signup_date), then for each day in that 7-day window collect the distinct user_ids who had an event that day. To preserve days with zero active users we generate days 0..6 and left-join.

SQL (Postgres):

sql

WITH cohorts AS (
  SELECT user_id,
         date_trunc('week', signup_date)::date AS cohort_week
  FROM users
),
events_by_day AS (
  SELECT e.user_id,
         date_trunc('day', e.occurred_at)::date AS event_date
  FROM events e
  JOIN cohorts c USING (user_id)
  -- restrict to the cohort week window to reduce scanned rows
  WHERE e.occurred_at >= c.cohort_week
    AND e.occurred_at < c.cohort_week + INTERVAL '7 days'
),
days AS (
  SELECT 0 AS day_number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
  SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
)
SELECT
  c.cohort_week,
  d.day_number,
  COALESCE(array_agg(DISTINCT eb.user_id ORDER BY eb.user_id), ARRAY[]::bigint[]) AS active_user_ids
FROM
  (SELECT DISTINCT cohort_week FROM cohorts) c
CROSS JOIN days d
LEFT JOIN events_by_day eb
  ON eb.event_date = c.cohort_week + (d.day_number || ' days')::interval
GROUP BY c.cohort_week, d.day_number
ORDER BY c.cohort_week, d.day_number;

Key points / reasoning:- We compute cohort_week from signup_date so each user belongs to one week-cohort.- events_by_day restricts events to that user’s cohort week to reduce data scanned.- array_agg(DISTINCT ...) returns unique active users per day; ORDER BY inside array_agg makes results deterministic.- CROSS JOIN days ensures we return day numbers 0–6 even if no events (left join + COALESCE yields empty arrays).

Performance & memory implications:- array_agg collects full lists in-memory per group; for large cohorts this can blow up memory and network transfer. If cohorts/days have millions of users, array_agg may cause query OOM or very large result payloads.- DISTINCT inside array_agg increases work (hashing or sorting). Sorting inside aggregation is extra CPU.- Scanning events joined to cohorts can be expensive; add indexes: events(occurred_at), events(user_id), users(signup_date). Consider partitioning events by date.- For very large scale prefer alternatives: returning counts (count(DISTINCT user_id)) or approximate sketches (HyperLogLog) for retention rates, storing pre-aggregated nightly tables, or paginating/storing user lists in object storage (Parquet/CSV) rather than returning huge arrays over SQL.- Use LIMIT or sampling when detailed lists aren't required. If you must produce lists, compress results (jsonb, or write to files) and ensure work_mem is tuned.

When appropriate:- This approach works well for small-to-medium cohorts (thousands of users) where you need exact lists for downstream analysis, debugging, or export.- Not appropriate for analytics at >100k users per cohort/day without pre-aggregation, batching, or switching to summary/approximate methods.

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.