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

89 practiced

Design an efficient SQL pattern to compute rolling 7-day retention: for each signup_date show what percent of those users returned at least once in the following 7-day window. Given users(user_id, signup_date) and events(user_id, event_time). Explain steps and write the query.

Sample Answer

Approach: create daily cohorts from users.signup_date, count cohort size, then count unique users who had at least one event in the 7-day window after signup (exclude the signup day unless you want to include it). Use a LEFT JOIN or EXISTS with date range filtering and aggregate by signup_date to compute percent_retention.

SQL (Postgres-compatible):

sql

WITH cohort AS (
  SELECT
    signup_date::date AS signup_date,
    user_id
  FROM users
),
events_in_window AS (
  SELECT DISTINCT
    c.signup_date,
    e.user_id
  FROM cohort c
  JOIN events e
    ON e.user_id = c.user_id
   AND e.event_time::date > c.signup_date            -- exclude signup day; use >= to include
   AND e.event_time::date <= c.signup_date + INTERVAL '7 days'
)
SELECT
  c.signup_date,
  COUNT(DISTINCT c.user_id)                        AS cohort_size,
  COALESCE(COUNT(DISTINCT e.user_id), 0)           AS retained_users_7d,
  CASE WHEN COUNT(DISTINCT c.user_id)=0 THEN 0
       ELSE ROUND(100.0 * COUNT(DISTINCT e.user_id) / COUNT(DISTINCT c.user_id), 2)
  END                                              AS pct_retention_7d
FROM cohort c
LEFT JOIN events_in_window e
  ON c.signup_date = e.signup_date
GROUP BY c.signup_date
ORDER BY c.signup_date;

Key points:- Using DISTINCT to avoid double-counting users with multiple events.- Date casting ensures comparisons are by day; adjust if event_time has timezone.- To include same-day returns, change the > to >=.- For large data, pre-aggregate events by (user_id, event_date) or use EXISTS to avoid large intermediate DISTINCTs; ensure appropriate indexes on events(user_id, event_time) for performance.- Edge cases: users with null signup_date, timezones, cohorts with zero users.

MediumTechnical

65 practiced

Explain practical differences between computing retention using left joins vs. using window functions and anti-joins. For a retention query that checks 'user returned in N days', when is each approach more efficient? Provide short pseudo-SQL examples.

Sample Answer

Clear overview: both patterns compute retention but behave differently on performance and readability depending on data size, cardinality, and indexing.

Left-join approach (find users with event in day0 then join to any event in day N):

sql

-- left join: simple and readable
WITH day0 AS (
  SELECT user_id, MIN(event_date) AS cohort_date
  FROM events WHERE event_date BETWEEN '2025-01-01' AND '2025-01-31'
  GROUP BY user_id
),
dayN AS (
  SELECT user_id, event_date FROM events
)
SELECT d0.cohort_date,
       COUNT(DISTINCT d0.user_id) AS cohort_size,
       COUNT(DISTINCT dN.user_id)   AS returned_on_dayN
FROM day0 d0
LEFT JOIN dayN dN
  ON d0.user_id = dN.user_id
  AND dN.event_date = DATE_ADD(d0.cohort_date, INTERVAL 7 DAY)
GROUP BY d0.cohort_date;

Window + anti-join (detect first return after cohort, or check absence/presence in a window):

sql

-- window to get next event per user, then filter
WITH events_ordered AS (
  SELECT user_id, event_date,
         LEAD(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id)
FROM events_ordered
WHERE event_date = '2025-01-01' -- cohort day
  AND next_event = DATE_ADD(event_date, INTERVAL 7 DAY);

Or anti-join to check no return within N days:

sql

SELECT d0.user_id
FROM day0 d0
LEFT JOIN events e
  ON d0.user_id = e.user_id
  AND e.event_date BETWEEN d0.cohort_date+1 AND d0.cohort_date+7
WHERE e.user_id IS NULL; -- did not return in N days

When each is more efficient:- Left-joins are simple and efficient when events table is modest size, properly indexed on (user_id, event_date), and you only need presence on exact day(s). DB can use index seeks and hash/merge joins.- Window functions excel when you need order-aware logic (first return, time-to-first-return) and you want to avoid large self-joins. They can be faster if the engine can stream-partition and memory for partitions fits; otherwise they may spill to disk.- Anti-joins (LEFT JOIN ... IS NULL or NOT EXISTS) are best for "did not return in N days" semantics and can be very efficient with good indexes because they short-circuit on existence.Trade-offs:- Joins can multiply rows (need DISTINCT or GROUP BY) causing higher memory/IO.- Window functions compute per-row state once, avoiding many-to-many explosion but may require sorting/partitioning.Guideline: For simple existence checks per cohort day use anti-join; for exact matching on specific future dates use left-join; for first-return / sequence problems use window functions. Always test with EXPLAIN and try filtering/partitions and proper indexes.

HardTechnical

78 practiced

Events arrive late and out-of-order. Propose a SQL-based strategy to compute accurate daily retention that supports backfills and ensures idempotency. Include deduplication strategy, reconciliation keys to detect updates, and an approach to update pre-aggregated daily retention tables.

Sample Answer

Approach (summary)- Ingest events into a raw staging table append-only.- Compute a canonical, deduplicated events table using a reconciliation key and event versioning/timestamp.- Use idempotent MERGE/UPSERT statements to update pre-aggregated daily retention tables. Support backfills by reprocessing date ranges idempotently.

Reconciliation & deduplication- Reconciliation key: (user_id, event_type, event_id_source). Also keep event_ts, received_ts, event_hash, and event_version (or last_modified_ts).- Dedupe logic: keep the row with the latest (event_version, event_ts, received_ts) and an event_hash to detect content changes.

Example: dedupe into canonical_events

sql

-- canonical_events has unique (user_id, event_type, event_id_source)
MERGE INTO canonical_events AS target
USING (
  SELECT user_id, event_type, event_id_source,
         MAX_BY(struct(event_ts, received_ts, event_hash, payload), (event_version, event_ts, received_ts)) AS latest
  FROM staging_events
  WHERE event_date BETWEEN :start AND :end
  GROUP BY user_id, event_type, event_id_source
) AS src
ON target.user_id = src.user_id AND target.event_id_source = src.event_id_source
WHEN MATCHED AND (target.event_hash <> src.latest.event_hash OR target.event_version < src.latest.event_version)
  THEN UPDATE SET event_ts = src.latest.event_ts, event_hash = src.latest.event_hash, event_version = src.latest.event_version, payload = src.latest.payload
WHEN NOT MATCHED THEN
  INSERT (...)

Retention pre-aggregate (daily_retention)- Define retention window (e.g., cohort_date = first_event_date). Compute daily active flags per cohort and day.- Recompute deltas from canonical_events for the reprocessed date range and MERGE into daily_retention using idempotent replaces.

Example: update daily retention for a date range

sql

WITH cohort AS (
  SELECT user_id, MIN(event_date) AS cohort_date
  FROM canonical_events
  WHERE event_type = 'signup' AND cohort_date BETWEEN :start_cohort AND :end_cohort
  GROUP BY user_id
),
activity AS (
  SELECT c.cohort_date, e.event_date,
         COUNT(DISTINCT e.user_id) AS active_users
  FROM cohort c JOIN canonical_events e ON c.user_id = e.user_id
  WHERE e.event_type = 'login' AND e.event_date BETWEEN :min_date AND :max_date
  GROUP BY c.cohort_date, e.event_date
)
MERGE INTO daily_retention dr
USING activity a
ON dr.cohort_date = a.cohort_date AND dr.activity_date = a.event_date
WHEN MATCHED THEN UPDATE SET active_users = a.active_users, updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN INSERT (cohort_date, activity_date, active_users, updated_at) VALUES (...)

Backfills & idempotency- Reprocess bounded date ranges only (cohort_date and activity_date windows). All operations are MERGE-based and deterministic.- Keep audit logging: job_id, processed_range, and checksum to avoid concurrent reprocessing.- For large reprocesses, use snapshot/versioning or partition-truncate-REPLACE for partitions to optimize.

Edge cases & best practices- Decide tie-breaker for identical versions (use received_ts).- Retain historical versions for audits if regulatory needs exist.- Monitor late-arrival lag and automate triggered backfills for windows beyond watermark.- Test with synthetic late/out-of-order events to validate idempotency.

EasyTechnical

76 practiced

Write a SQL query to compute weekly acquisition cohorts: for each cohort_week (based on users.signup_date truncated to week) return cohort_size (number of users) for the last 12 weeks. Given users(user_id STRING, signup_date DATE, acquisition_channel STRING). Include channel in the output (cohort_week, acquisition_channel, cohort_size).

Sample Answer

Approach: bucket users by the week of their signup_date (cohort_week) and acquisition_channel, restrict to the last 12 weeks (including current week), then count users per group.

sql

SELECT
  date_trunc('week', signup_date)::date AS cohort_week,
  acquisition_channel,
  COUNT(DISTINCT user_id) AS cohort_size
FROM users
WHERE signup_date >= date_trunc('week', current_date) - INTERVAL '11 weeks'
  AND signup_date < date_trunc('week', current_date) + INTERVAL '1 week'  -- optional upper bound
GROUP BY 1, 2
ORDER BY cohort_week DESC, acquisition_channel;

Key points:- date_trunc('week', signup_date)::date normalizes signup_date to the week start (Postgres). Adjust function for your dialect (e.g., TRUNC(...,'IW') in Oracle, DATE_TRUNC in Snowflake/Redshift).- COUNT(DISTINCT user_id) avoids duplicates if users appear multiple times.- WHERE clause restricts to the most recent 12 weeks (current week + previous 11). Change intervals if you want last 12 complete weeks (exclude current partial week).Edge cases:- Timezone-aware columns — cast/convert to date first if timestamps.- If you need weeks with zero signups, LEFT JOIN against a generated calendar of weeks × channels to show zeros.

MediumTechnical

93 practiced

Design a reproducible SQL notebook workflow to onboard a new growth metric (e.g., 'sticky users' defined as users with >=5 active days in 28 days). Include data validation queries, transformation steps, aggregation queries, and automated tests you'd run before shipping to dashboard.

Sample Answer

Requirements:- Definition: "sticky user" = user with >=5 active days in a 28-day window.- Reproducible notebook: parameterize window_end_date and window_size(28).- Deliverables: validated cleaned daily_activity table → transformed user-window table → aggregated metric by date/cohort → tests.

Notebook sections & example queries:

1) Params (SQL variables or notebook cell)window_end := '2025-11-30';window_size := 28;

2) Data validation (source: daily_activity(user_id, activity_date, event_count))Check schema & freshness:

sql

-- rows in source
SELECT COUNT(*) AS total_rows, MAX(activity_date) AS last_date FROM raw.daily_activity;
-- nulls and types
SELECT
  SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) AS null_user_id,
  SUM(CASE WHEN activity_date IS NULL THEN 1 ELSE 0 END) AS null_date
FROM raw.daily_activity;

3) Filter & dedupe

sql

WITH activity AS (
  SELECT DISTINCT user_id, DATE(activity_date) AS activity_date
  FROM raw.daily_activity
  WHERE activity_date BETWEEN DATEADD(day, - (window_size-1), :window_end) AND :window_end
)
SELECT COUNT(*) FROM activity;

4) Transformation: compute active_days per user in window

sql

WITH activity AS (...above...)
SELECT
  user_id,
  COUNT(DISTINCT activity_date) AS active_days
INTO temp.user_window_activity
FROM activity
GROUP BY user_id;

5) Label sticky users

sql

SELECT
  user_id,
  active_days,
  CASE WHEN active_days >= 5 THEN 1 ELSE 0 END AS is_sticky
INTO temp.user_window_sticky
FROM temp.user_window_activity;

6) Aggregation for metric timeseries (daily rolling windows)

sql

-- For a given window_end
SELECT
  :window_end AS window_end,
  COUNT(*) AS total_users_in_window,
  SUM(is_sticky) AS sticky_users,
  SAFE_DIVIDE(SUM(is_sticky), COUNT(*)) AS sticky_rate
FROM temp.user_window_sticky;

7) Automated tests before shipping- Row-count regression: compare total_users_in_window to prior similar window (±10% tolerance).

sql

-- previous window_end = DATEADD(day, -1, :window_end)

- Nulls and types: ensure user_id non-null, active_days >=0.- Boundary test: users with exactly 5 active days flagged sticky.

sql

SELECT user_id FROM temp.user_window_sticky WHERE active_days = 5 AND is_sticky <> 1;

- Sampling correctness: pick random user and cross-check raw events

sql

SELECT activity_date FROM raw.daily_activity WHERE user_id = 'SAMPLE_ID' AND activity_date BETWEEN ...;

- Idempotence: re-running pipeline yields same counts (use checksum)

sql

SELECT MD5(STRING_AGG(CONCAT(user_id,'|',active_days) ORDER BY user_id)) AS fingerprint FROM temp.user_window_activity;

8) Deployment checklist- Add metric docs (definition, window, owners)- Add unit tests to CI (run notebook or SQL jobs)- Add alert on test failures and metric drift

Why this approach:- Parameterized windows make metric reproducible and backfillable.- Validations catch ETL regressions early.- Fingerprint + regression checks ensure deterministic runs.- Sampling confirms logical correctness before dashboarding.

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.