Why this sub-area matters
Cohort retention and attribution questions test more than SQL syntax. They reveal whether you can turn an ambiguous business question into a precise analytical definition, preserve that definition through joins and time boundaries, and explain what the resulting number does—and does not—mean.
A strong answer starts with the population and the grain. It then states the time rules, numerator, denominator, and treatment of late or duplicate events. Only after that should you reach for a window function or write a query.
The questions below use PostgreSQL-flavored SQL. The definitions are illustrative, not universal: in an interview, make the assumptions explicit and ask whether they match the business question.
Fundamentals
1. What does “grain” mean, and why establish it before writing an analytics query?
What the interviewer is really testing: Whether you understand what one row represents and can spot when a join or aggregation changes that meaning.
Model answer:
- Grain is the real-world unit represented by a row: for example, one event, one order line, one order, or one user-day.
- State it before describing columns. A fact table with one row per order line is not interchangeable with one row per order; summing an order-level amount after joining to lines can count it multiple times.
- Identify the grain of every source and the grain of the result. Then check whether joins preserve, reduce, or multiply rows.
- For retention, a useful intermediate grain is one row per
(user_id, activity_date). That prevents several events from the same user on the same day from counting as several retained users.
Follow-up: How would you detect a join that multiplies order-level revenue?
Hint: Compare row counts and distinct order counts before and after the join.
2. How do you define a cohort and its denominator?
What the interviewer is really testing: Whether you can separate cohort membership from later activity and avoid changing the denominator accidentally.
Model answer:
A cohort groups entities by a shared starting event or characteristic—for example, users by the date of their first qualifying purchase. Define the qualifying event, the entity key, and the time zone before calculating anything.
The cohort size is the count of distinct eligible entities assigned to that cohort. For day-n retention, the numerator is the number of those entities active on the specified relative day; the denominator remains the original cohort size. It should not shrink to the users still active on the previous day unless the question explicitly asks for conditional retention.
Be careful with “new user.” It might mean first account creation, first app session, first paid transaction, or first event observed in the warehouse. Those definitions create different cohorts.
Follow-up: What changes if users can qualify for more than one cohort?
Hint: Decide whether cohort membership is exclusive or whether the analysis intentionally allows overlapping populations.
3. When would you use a window function instead of GROUP BY?
What the interviewer is really testing: Whether you understand the difference between reducing rows and calculating across rows while preserving them.
Model answer:
GROUP BY collapses rows to one row per group. A window function calculates over a related set of rows but leaves each input row available in the result. Use aggregation when the output should have one row per group; use a window when each row needs context such as a rank, running total, or previous value.
For example, a running total needs a deterministic ordering and usually an explicit frame. Without one, the result may not express the intended calculation, especially when the ordering column has ties.
WITH daily_sales(day, amount) AS (
VALUES
(DATE '2026-01-01', 10),
(DATE '2026-01-02', 20),
(DATE '2026-01-03', 5)
)
SELECT
day,
amount,
SUM(amount) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_amount
FROM daily_sales
ORDER BY day;
Notice: GROUP BY would return one row per group; this window calculation retains each day and adds its cumulative total.
Follow-up: Why might you add a tie-breaker to the window’s ORDER BY?
Hint: Think about whether two rows can share the same ordering value.
4. What is the difference between event time and ingestion time?
What the interviewer is really testing: Whether you can reason about delayed data and define which clock controls an analysis.
Model answer:
Event time is when something happened according to the event’s timestamp. Ingestion time is when the data platform received or recorded it. They can differ because of offline clients, retries, buffering, or pipeline delays.
For user behavior and cohort membership, event time is often the relevant clock. Ingestion time is useful for freshness monitoring and debugging. A late-arriving event may change a historical cohort or its retention, so dashboards should state how recently their data is complete and whether recent periods are provisional.
Store both timestamps when possible. Also define a time zone and a policy for invalid or implausibly future-dated events; otherwise, the same record can land in different reporting periods in different parts of the system.
Follow-up: How would you handle a late event that changes a user’s first-purchase date?
Hint: Consider recomputing affected users or affected date partitions rather than silently keeping the old assignment.
Intermediate
5. Write a daily cohort-retention query and explain its assumptions.
What the interviewer is really testing: Whether you can translate the cohort definition into correct SQL and make the calendar logic visible.
Model answer:
First specify the cohort event, activity event, time zone, and retention definition. This example assigns each user to their first signup date and counts whether they had a session_start on each exact calendar day afterward. It uses UTC calendar dates and deduplicates repeated copies of the same event ID.
The query also generates day offsets 0–2 for every cohort. That keeps a cohort-day row even when nobody returned on that day. In a production model, you would also define how to handle incomplete recent cohorts: a missing future day is not necessarily zero retention.
CREATE TEMP TABLE events (
event_id text,
user_id text,
event_name text,
event_ts timestamptz
);
INSERT INTO events VALUES
('e1', 'u1', 'signup', '2026-01-01 09:00+00'),
('e2', 'u1', 'session_start','2026-01-01 09:10+00'),
('e3', 'u1', 'session_start','2026-01-02 10:00+00'),
('e4', 'u2', 'signup', '2026-01-01 11:00+00'),
('e5', 'u2', 'session_start','2026-01-03 11:00+00'),
('e6', 'u3', 'signup', '2026-01-02 08:00+00');
WITH deduped AS (
SELECT DISTINCT ON (event_id)
event_id, user_id, event_name, event_ts
FROM events
ORDER BY event_id, event_ts
),
first_signup AS (
SELECT
user_id,
MIN((event_ts AT TIME ZONE 'UTC')::date) AS cohort_date
FROM deduped
WHERE event_name = 'signup'
GROUP BY user_id
),
activity AS (
SELECT DISTINCT
user_id,
(event_ts AT TIME ZONE 'UTC')::date AS activity_date
FROM deduped
WHERE event_name = 'session_start'
),
cohort_sizes AS (
SELECT cohort_date, COUNT(*) AS cohort_users
FROM first_signup
GROUP BY cohort_date
),
retained AS (
SELECT
s.cohort_date,
(a.activity_date - s.cohort_date) AS day_number,
COUNT(DISTINCT s.user_id) AS retained_users
FROM first_signup s
JOIN activity a USING (user_id)
WHERE a.activity_date BETWEEN s.cohort_date
AND s.cohort_date + 2
GROUP BY s.cohort_date, a.activity_date - s.cohort_date
),
offsets AS (
SELECT generate_series(0, 2) AS day_number
)
SELECT
c.cohort_date,
o.day_number,
c.cohort_users,
COALESCE(r.retained_users, 0) AS retained_users,
COALESCE(r.retained_users, 0)::numeric / c.cohort_users
AS retention_rate
FROM cohort_sizes c
CROSS JOIN offsets o
LEFT JOIN retained r
ON r.cohort_date = c.cohort_date
AND r.day_number = o.day_number
ORDER BY c.cohort_date, o.day_number;
Notice: activity is reduced to one row per user and date, while the cohort denominator is calculated separately and stays fixed.
Follow-up: How would you modify this for “returned at least once on or after day 7”?
Hint: That is a rolling-retention definition, not exact-day retention.
6. What is the difference between exact-day retention and rolling retention?
What the interviewer is really testing: Whether you can distinguish two metrics that often share the same label.
Model answer:
Exact-day retention asks whether a user was active on one specific relative day—for example, day 7. Rolling retention asks whether they were active on or after that day, within a defined observation window or through the end of available data.
Exact-day retention is useful for understanding cadence, but can be sensitive to which days users tend to use a product. Rolling retention answers a broader “came back again” question, but its value depends on the cutoff: a user with more time observed has more opportunity to return.
Name the definition in the metric or dashboard, such as “day 7 exact” or “returned on or after day 7 through day 30.” Do not compare the values as if they were equivalent.
Follow-up: Why can a recent cohort’s rolling retention appear lower than an older cohort’s?
Hint: Check whether both cohorts have the same length of observation.
7. How would you deduplicate repeated events without discarding legitimate activity?
What the interviewer is really testing: Whether you can distinguish retries from separate actions and choose a stable event identity.
Model answer:
Prefer a producer-supplied idempotency key or immutable event ID that identifies one logical event. Deduplicate on that key, choosing a deterministic record if multiple copies arrive. If no stable identifier exists, a composite key may be a fallback, but it can merge two legitimate events that happen to share the same attributes.
Do not deduplicate merely by (user_id, event_name, date) if multiple actions on that date are meaningful. For a user-day retention calculation, however, reducing valid events to one row per user and date is appropriate because the metric asks whether the user was active, not how many events they generated.
Document whether corrections or updates replace earlier copies, and monitor duplicate rates separately from business metrics.
Follow-up: What would you do if the source has no event ID?
Hint: Explain the risk of a composite key and discuss improving the event contract at its source.
8. Compare first-touch, last-touch, and multi-touch attribution.
What the interviewer is really testing: Whether you recognize that attribution is a rule for assigning credit, not proof of causal impact.
Model answer:
First-touch assigns credit to the first eligible touch in a conversion’s lookback window. Last-touch assigns it to the last eligible touch. Both are simple to explain, but each discards information about other touches and can produce very different channel rankings.
Multi-touch rules distribute credit among eligible touches—for example, evenly or with position-based weights. They capture more of the path but depend on chosen weights and can be harder to explain. Every model needs rules for the conversion event, eligible channels, identity stitching, lookback window, direct traffic, and multiple conversions.
Attribution describes credit under a chosen model. It does not establish what would have happened without a touch; causal questions generally require an experimental or other causal design.
Follow-up: How would you handle a conversion with no eligible marketing touch?
Hint: Define an explicit unattributed or direct category instead of dropping it.
9. Why can a join produce inflated user counts or revenue?
What the interviewer is really testing: Whether you can reason about join cardinality and aggregation order.
Model answer:
A join can multiply rows when one record on the left matches several on the right. For example, joining one order to three campaign touches produces three joined rows. Summing the order amount after that join counts the order three times.
Check the declared and observed cardinality on both sides. Aggregate a many-side to the intended grain before joining when appropriate, or use an allocation rule if the measure should be distributed. COUNT(DISTINCT user_id) can protect a user count in some queries, but it does not repair duplicated revenue or explain why the join multiplied rows.
Describe the expected grain after each join. If you cannot, test with a small example that has one-to-many matches.
Follow-up: When is COUNT(DISTINCT ...) a warning sign rather than a fix?
Hint: Ask whether it hides an incorrect join or an undefined output grain.
Senior / system-design level
10. How would you design cohort reporting when user identities can merge?
What the interviewer is really testing: Whether you can design for changing identity resolution and explain its effect on historical results.
Model answer:
Separate the raw event identity from the resolved person identity. Keep source identifiers and the mapping that connects them, with effective times or version history if identity relationships can change. Then define whether the analysis uses the identity known at event time or the latest resolved identity.
If two accounts are later determined to belong to one person, historical cohort sizes and activity can change. That is a business rule, not just a database optimization. Preserve enough lineage to explain a change, and make the reporting policy clear: restate history, freeze prior results, or provide both views.
For large reports, materialize a stable user-day or user-cohort layer, but retain a way to rebuild it when identity mappings change.
Follow-up: How would you make a historical retention result reproducible?
Hint: Version the identity mapping and transformation logic used to produce it.
11. How would you analyze an experiment’s effect on retention?
What the interviewer is really testing: Whether you can preserve experiment assignment and eligibility rather than counting only observed activity.
Model answer:
Start with the assignment unit and eligibility rule. If assignment is per user, retain one assignment record per user and use that population as the analysis denominator. Define the retention outcome and observation window before looking at results. A user with no qualifying activity may be a zero outcome, not a missing row.
Check allocation and data quality before interpreting differences. For example, investigate sample-ratio mismatch, duplicate assignment, exposure timing, and whether the outcome window has completed for every included user. Avoid joining to activity in a way that removes users who did not return.
The primary analysis should follow the experiment’s intended design. If the question is causal, state the assumptions and guard against post-assignment filtering that can bias the comparison.
Follow-up: What does sample-ratio mismatch suggest you should investigate?
Hint: Look for assignment, logging, eligibility, or filtering problems before explaining a treatment effect.
12. How would you make cohort queries perform well as data grows?
What the interviewer is really testing: Whether you can improve performance while preserving the metric definition.
Model answer:
First inspect the query plan and data layout; do not assume a particular index or partitioning strategy will help without checking the workload. Common opportunities include filtering early on relevant event types and time ranges, reducing repeated events to the required grain, and avoiding unnecessary columns and joins.
For frequently requested reporting, consider a maintained user-day or cohort-activity table. It can reduce repeated scans, but adds freshness, backfill, and correction responsibilities. Ensure incremental updates handle late-arriving events and changes to first-event assignment.
Keep the output at the grain consumers need. Pre-aggregating too early can make later breakdowns impossible; not pre-aggregating at all can make every dashboard repeat expensive work.
Follow-up: What should you verify before replacing a raw-event query with a summary table?
Hint: Compare results across late data, backfills, and the dimensions consumers need.
13. How would you validate an attribution or retention model before publishing it?
What the interviewer is really testing: Whether you treat analytical outputs as software with testable assumptions and operational behavior.
Model answer:
Test both invariants and examples. Invariants might include: each user has at most one assigned first-event cohort; exact-day retained users cannot exceed cohort size; and attribution weights sum to one per conversion when the model promises full allocation. Example tests should cover duplicate events, no-return users, boundary dates, multiple touches, and users with missing identity mappings.
Reconcile counts to source data at clearly defined grains. When a dashboard changes, compare the old and new definitions on a fixed time range and explain expected differences. Keep the SQL or transformation version and its inputs discoverable so a result can be investigated later.
Finally, decide how incomplete data appears. A cohort whose day-7 observation window is not complete should not be presented as an ordinary zero without an explicit policy.
Follow-up: What invariant would you test for a fractional multi-touch model?
Hint: Check the total assigned credit per conversion.
Choosing between close alternatives
| Decision | Alternative A | Alternative B | Choose based on |
|---|---|---|---|
| Retention definition | Exact-day | Rolling | Whether the question is about a specific cadence or any return after a threshold |
| Attribution | First/last-touch | Multi-touch | Simplicity and interpretability versus distributing credit across a path |
| Aggregation | GROUP BY | Window function | Whether the output should collapse rows or retain row-level detail |
| Event identity | Stable event ID | Composite deduplication key | Whether the source provides a reliable logical-event identifier |
| Cohort history | Restate with latest identity | Preserve identity as known at the time | Whether current truth or reproducibility of prior reporting is required |
Common mistakes candidates make
- Writing SQL before agreeing on the qualifying event, entity key, time zone, and observation window.
- Reporting a retention percentage without stating its numerator and denominator.
- Treating exact-day and rolling retention as interchangeable.
- Using ingestion time as event time without discussing late arrivals.
- Counting events when the metric asks for users, or users when it asks for events.
- Joining a one-to-many table and summing measures without checking the resulting grain.
- Using
COUNT(DISTINCT ...)to conceal a join problem instead of diagnosing it. - Calling attribution “impact” without evidence for a causal interpretation.
- Dropping users with no activity because the query starts from an activity table.
- Treating incomplete future observation windows as zero outcomes.
- Suggesting a pre-aggregation without explaining how corrections and backfills reach it.
- Giving a confident metric answer while leaving its assumptions implicit.
Quick-revision checklist
Before you answer an interview question, check that you can:
- State the grain of the input and output.
- Define the cohort event, activity event, entity, and time zone.
- Name the retention denominator and exact retention rule.
- Explain how late and duplicate events are handled.
- Predict the cardinality of each join.
- Distinguish attribution credit from causal impact.
- Keep non-returning or unexposed users in the right denominator.
- Describe tests, reconciliation, and incomplete-window behavior.
- Explain performance trade-offs without changing the metric definition.
A concise, defensible definition followed by correct SQL is stronger than a sophisticated query built on unstated assumptions.
This article was generated with AI. #AIGenerated