SQL Interview: Calculate Exact Day-7 Cohort Retention

Prompt and Applicable Context

You are given two PostgreSQL tables:

CREATE TABLE users (
  user_id bigint PRIMARY KEY,
  signup_at timestamptz NOT NULL,
  acquisition_channel text
);

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  event_at timestamptz NOT NULL,
  event_name text NOT NULL
);

For users whose signup date falls from January 1 through January 31, 2026, report exact Day-7 retention grouped by signup date and acquisition channel. The business time zone is America/New_York. Day 0 is the user's local signup date. A user is retained on Day 7 if they have at least one coreactioncompleted event on the local calendar date signup_day + 7. Events on Days 1–6, Day 8, or any later date do not satisfy this definition.

Return cohortday, acquisitionchannel, cohortsize, retainedusers, and d7retentionrate. Multiple qualifying events count once per user. Users with no return event must remain in the denominator. Null channels are grouped as unknown.

Assume datacompletethrough = '2026-02-01 05:00:00+00'. This is an exclusive ingestion watermark and equals midnight at the start of February 1 in New York. Only include a signup cohort when its entire Day-7 calendar date is before that watermark. The January 24 cohort is mature because its Day 7 is January 31; the January 25 cohort is not.

This problem is useful in analytics-engineer, data-analyst, and product-analytics interviews because the difficult part is the metric contract, not the division. The query must align the cohort grain, return window, time zone, data completeness, and segmentation before it aggregates anything.

What the Interviewer Evaluates

The first signal is whether the candidate asks what “Day-7 retention” means. Three metrics that sound similar are materially different: activity exactly on Day 7, activity at any point during Days 1–7, and activity on Day 7 or later. A query can be syntactically perfect and still answer the wrong one. This prompt requires the first definition.

The second signal is denominator discipline. The denominator is every eligible user in a mature signup cohort. Starting from the events table, or inner-joining returns to signups, silently removes users who never returned and overstates retention. The cohort must be built first and preserved with a left join.

The third signal is grain control. The output has one row per signup date and channel, while the retention flag has at most one row per user. Raw event data may contain retries, duplicates, and many valid actions on the same date. Counting event rows would measure action volume rather than retained users, so the numerator must be deduplicated at the user level.

The fourth signal is temporal correctness. A local calendar day is not always a fixed 24-hour interval and a named zone can change UTC offset under daylight-saving rules. Deriving a local date first and building each user's target-day bounds from named-zone midnights expresses the contract directly. A test such as eventat = signupat + INTERVAL '7 days' answers an elapsed-time question instead.

The final signal is operational judgment. Recent cohorts need a complete observation window, and a wall clock does not prove that an event pipeline is complete. A candidate should use a data watermark, explain late-arriving events, validate the smallest reliable reporting grain, and choose indexes or a daily activity table according to query frequency and data volume.

Questions to Clarify Before Answering

• Is Day 7 exact, bounded, or rolling? This answer uses an event exactly on the seventh local date

after signup. “Within seven days” and “on or after Day 7” require different predicates.

• What event proves retention? The prompt uses coreactioncompleted. Login, page view, purchase,

or any event would produce different product meaning and should not be substituted casually.

• What defines a day? The reporting contract uses America/New_York calendar dates. Per-user time

zones or UTC would change cohort membership and return windows.

• When is a cohort mature? A cohort is reportable only when the exclusive data watermark is at or

beyond the next local midnight after that cohort's Day 7.

• How are late events handled? If the watermark later advances or backfilled events arrive before

it, affected cohorts must be recomputed. The displayed rate should not be treated as immutable.

• Which channel value applies? The query assumes users.acquisition_channel is the immutable

signup-time attribution. A mutable current channel needs a versioned attribution snapshot instead.

• What is the cohort grain? This prompt groups by signup date and channel. Weekly cohorts use the

same user-level logic but a different final grouping key.

• Should the rate be a fraction or percentage? The query returns a fraction rounded to four decimal

places: 0.3333 means 33.33%.

30-Second Answer Framework

“I would define exact Day 7 as the user's signup calendar date plus seven in the agreed business time zone. First I build the January cohort from local-midnight UTC bounds and store one row per user with signup day and signup-time channel. Then I exclude cohorts whose full target day is beyond the exclusive data watermark. For each remaining user, I look for the target event in the half-open local Day-7 interval, deduplicate to one retained row per user, and left-join that flag back to the mature cohort. Finally I count cohort users and retained users by date and channel. I would test duplicates, zero returns, Day-6 and Day-8 events, local midnights, DST boundaries, and the maturity cutoff.”

Step-by-Step Deep Dive

Start with parameters that make the reporting contract visible. cohort_end is exclusive, and the watermark is the first unobserved instant. Half-open intervals avoid double-counting an event at midnight and compose cleanly across adjacent dates.

WITH params AS (
  SELECT
    'America/New_York'::text AS tz,
    DATE '2026-01-01' AS cohort_start,
    DATE '2026-02-01' AS cohort_end,
    TIMESTAMPTZ '2026-02-01 05:00:00+00' AS data_complete_through
),
cohort AS (
  SELECT
    u.user_id,
    COALESCE(u.acquisition_channel, 'unknown') AS acquisition_channel,
    (u.signup_at AT TIME ZONE p.tz)::date AS signup_day
  FROM users AS u
  CROSS JOIN params AS p
  WHERE u.signup_at >= (p.cohort_start::timestamp AT TIME ZONE p.tz)
    AND u.signup_at < (p.cohort_end::timestamp AT TIME ZONE p.tz)
),
mature_cohort AS (
  SELECT c.*
  FROM cohort AS c
  CROSS JOIN params AS p
  WHERE c.signup_day + 7
    < (p.data_complete_through AT TIME ZONE p.tz)::date
),
retained AS (
  SELECT DISTINCT c.user_id
  FROM mature_cohort AS c
  CROSS JOIN params AS p
  JOIN events AS e
    ON e.user_id = c.user_id
   AND e.event_name = 'core_action_completed'
   AND e.event_at >= ((c.signup_day + 7)::timestamp AT TIME ZONE p.tz)
   AND e.event_at < ((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
)
SELECT
  c.signup_day AS cohort_day,
  c.acquisition_channel,
  COUNT(*) AS cohort_size,
  COUNT(r.user_id) AS retained_users,
  ROUND(
    COUNT(r.user_id)::numeric / NULLIF(COUNT(*), 0),
    4
  ) AS d7_retention_rate
FROM mature_cohort AS c
LEFT JOIN retained AS r
  ON r.user_id = c.user_id
GROUP BY c.signup_day, c.acquisition_channel
ORDER BY c.signup_day, c.acquisition_channel;

The cohort filter converts the January local-midnight boundaries to UTC instants before comparing them with indexed timestamptz values. This is preferable to applying a date conversion to every signup_at row in the WHERE clause: it states the local-date rule while keeping the timestamp column eligible for a normal range scan.

Maturity is easier to reason about in dates. The watermark converts to local date February 1. A target date must be strictly earlier than February 1, which means its closing midnight is covered. January 24 plus seven is January 31 and passes. January 25 plus seven is February 1 and fails. If the watermark were midday rather than a local midnight, the robust form would compare the target day's ending instant directly with the watermark:

((c.signup_day + 8)::timestamp AT TIME ZONE p.tz)
  <= p.data_complete_through

The retained CTE creates a semi-join-like user flag. DISTINCT makes each user contribute at most one row even if the event producer retried or the user completed the core action ten times. Because a user belongs to exactly one cohort row, joining by user_id is sufficient under the stated schema. If the same person could have multiple signup episodes, the model would need a stable episode identifier and the join would use it.

The final left join preserves every mature cohort member. COUNT(*) therefore measures the denominator, while COUNT(r.user_id) counts only matched retained flags. NULLIF is defensive at the group level, although a group produced from mature_cohort necessarily contains at least one row.

For a small fixture, suppose the January 1 organic cohort has three users and only one has a qualifying Day-7 action. Its result is 3, 1, and 0.3333. If the January 2 paid cohort has two users and both return, its result is 2, 2, and 1.0000. A January 25 signup is absent regardless of later events because that cohort is immature at the supplied watermark.

Do not average the displayed group rates to create a total. A daily cohort of one user must not carry the same weight as a cohort of one thousand. The correct rollup is:

overall_retention = SUM(retained_users) / SUM(cohort_size)

On production-sized data, useful indexes match the selective bounds and join keys:

CREATE INDEX users_signup_at_idx
  ON users (signup_at);

CREATE INDEX events_core_action_user_time_idx
  ON events (user_id, event_at)
  WHERE event_name = 'core_action_completed';

The partial event index is appropriate only when this event definition is stable and important enough to justify its write and storage cost. For a recurring dashboard over a very large event stream, an incrementally maintained table with a unique key such as (userid, activityday, event_name) can remove repeated raw-event scans. Its activity_day must be derived under the same named-zone contract; otherwise the optimization changes the metric.

Let U be eligible users and E be relevant event rows examined through the join. The cohort scan is linear in the selected users, deduplication is typically a hash or sort over retained candidates, and the final aggregation is linear in mature users. Actual cost depends on event selectivity, indexes, statistics, and plan shape, so inspect EXPLAIN (ANALYZE, BUFFERS) on representative data instead of promising a universal complexity from SQL text alone.

High-Quality Sample Answer

“Before writing SQL, I would lock four definitions: exact Day 7, coreactioncompleted as the return event, New York calendar dates, and an exclusive completeness watermark. The denominator is every January signup whose whole Day-7 date has been observed; the numerator is distinct users from that set with at least one qualifying event.

I would build the cohort first. I convert January's local start and end midnights to UTC instants for the signup_at range filter, then retain each user's local signup date and signup-time channel. I filter maturity separately so it can be audited: with a February 1 local-midnight watermark, January 24 is the last included signup date.

For each mature user, I join events on user_id, the target event name, and the half-open interval from local midnight on signupday + 7 to local midnight on signupday + 8. Those bounds are calculated in the named time zone, so a DST transition does not turn a calendar rule into a 168-hour rule. I select distinct user IDs, left-join the retained flags to the cohort, and aggregate by signup date and channel. That left join is essential because users with no return still belong in the denominator.

I would validate the CTEs independently. The cohort CTE must have one row per January signup; the maturity CTE must stop at January 24 for this watermark; the retained CTE must have unique users; and the final counts must satisfy 0 <= retainedusers <= cohortsize. Fixtures would cover duplicate events, no return, wrong event names, Day 6 and Day 8, both sides of local midnight, a DST date, null channels, and a late event backfill. For a total across groups, I would divide summed retained users by summed cohort users rather than average the rates.”

Common Mistakes

• Using an inner join. This discards zero-return users and inflates the rate. Build the cohort first

and left-join a user-level retained flag.

• Counting events instead of users. COUNT(e.event_id) can exceed the cohort size. Deduplicate the

numerator by user or use a boolean existence test.

• Leaving Day 7 ambiguous. A predicate covering Days 1–7 or Day 7 onward computes a different

metric. Write the interval definition in words before SQL.

• Comparing to signup timestamp plus 168 hours. That is elapsed-time retention, not a named-zone

calendar-date definition, and it can diverge around daylight-saving changes.

• Casting indexed timestamps inside the filter. Converting every signup_at to a date may prevent a

useful range scan. Convert the constant local boundaries to instants instead.

• Including immature cohorts. Recent users have not had a full opportunity to return, creating

artificial underperformance. Gate on the pipeline watermark, not merely the current time.

• Treating a watermark as permanent truth. Backfills can change already reported cohorts. Define

recomputation and freshness behavior.

• Reading a mutable channel value. Current attribution can leak future information into historical

cohorts. Use an immutable signup field or versioned snapshot.

• Averaging group percentages. Unweighted averages distort totals when cohort sizes differ. Roll up

the counts first.

• Ignoring empty and null semantics. Map null channels deliberately, and decide whether missing

event data means zero activity or an incomplete pipeline before publishing the metric.

Follow-Up Questions and Responses

How would you calculate retention within Days 1–7?

Keep the same cohort and maturity approach, but change the event interval to start at local midnight on signupday + 1 and end before local midnight on signupday + 8. Maturity still requires the whole seventh day to be complete. State whether Day 0 should count; product tools and teams differ on this.

How would you calculate “Day 7 or later” retention?

The lower bound remains local midnight on signup_day + 7, but the upper bound becomes the reporting cutoff. That metric is cumulative and depends on observation length: an older cohort has more chances to return. Compare cohorts only at a common age or publish a retention curve rather than one uncapped number.

Can PostgreSQL aggregate this without a separate retained CTE?

Yes. A lateral EXISTS lookup or a carefully constructed aggregate can return one boolean per cohort user. A direct join plus COUNT(DISTINCT e.user_id) FILTER (WHERE ...) is also possible, but it can materialize many event rows before aggregation. The separate CTE makes the grain and correctness proof easy to audit; choose the final plan from measured data.

What if each user has a different reporting time zone?

Store the zone that applies to the signup episode and derive both cohort day and target boundaries from that same value. Historical zone changes need a stated policy. Per-user zones also mean that one calendar cohort label spans different UTC intervals, so pre-aggregations must retain the applicable zone or already normalized local day.

How would you test the maturity boundary?

Create signups on January 24 and January 25 under the supplied watermark. Give both a valid target-day event. January 24 must appear and January 25 must not. Also test a watermark one second before and exactly at the target day's closing midnight to confirm the exclusive-boundary rule.

How should late-arriving events be handled?

Publish the event-time watermark and recompute all cohorts whose eligible event intervals overlap a backfill. If ingestion lateness has a known service level, reports can add a safety delay beyond the nominal Day-7 close. Keep the raw count and metric version so a correction is traceable.

What invariants would you monitor in production?

Check that every group has positive cohort_size, retained users stay between zero and cohort size, no immature cohort date is present, cohort totals reconcile with the signup source, and the maximum event time covers the declared watermark. Alert separately on event-volume or ingestion-delay anomalies so a pipeline gap is not misread as a retention decline.