How do you calculate day-7 retention?

Divide the number of eligible users who return on the calendar date seven days after signup by the number of users whose day-7 observation period is complete. Multiply by 100 for a percentage. This guide uses exact-day retention: returning on day 8 does not qualify. A different definition, such as returning at any time in the first week, needs a different query.

The dataset below is synthetic. It represents a small US subscription product using America/New_York reporting dates. It is a learning exercise, not an industry benchmark or a record of an employer’s interview questions.

Agree on the metric before writing SQL

Suppose a product manager asks, “Are new customers coming back?” Ask what counts as coming back. A background notification event may not represent useful activity; opening a project or completing an analysis may be a better qualifying event. For this example, the activity table already contains only qualifying events.

Use one signup row per stable user ID. Convert signup and activity timestamps into the same reporting time zone before extracting their dates. The query starts with prepared local dates so you can focus on cohort logic. If you need to prepare them from UTC timestamps, first read the US time-zone and daily-user exercise.

Our reporting cutoff is the start of September 9, 2026. Events are complete through September 8. That completeness assumption matters: a pipeline delay could make an apparently finished day incomplete.

Work through four users by hand

• User a: signed up September 1 and generated two events September 8. Count one retained user.
• User b: signed up September 1 and returned September 9. This is day 8, so the user is not retained on exact day 7.
• User c: signed up September 1 and has no return event. Keep this user in the denominator.
• User d: signed up September 2. Day 7 is September 9, which is not complete at the cutoff. Exclude this user from both numerator and denominator.

For the September 1 cohort, the expected result is one retained user out of three eligible users: 33.3%. The September 2 cohort should not appear yet. Excluding an immature cohort is different from reporting zero retention.

Run the complete SQLite query

This self-contained example runs in SQLite, including Python’s built-in sqlite3 module. No database account or external data is required. The date syntax is SQLite-specific; do not paste it unchanged into every warehouse.

WITH signups(user_id, signup_day) AS (
 VALUES ('a','2026-09-01'),('b','2026-09-01'),
        ('c','2026-09-01'),('d','2026-09-02')
), activity(user_id, activity_day) AS (
 VALUES ('a','2026-09-08'),('a','2026-09-08'),
        ('b','2026-09-09'),('d','2026-09-09')
), targets AS (
 SELECT user_id, signup_day,
        date(signup_day, '+7 days') AS target_day
 FROM signups
 WHERE date(signup_day, '+7 days') < '2026-09-09'
), returns AS (
 SELECT DISTINCT user_id, activity_day FROM activity
)
SELECT t.signup_day, COUNT(*) AS eligible_users,
       COUNT(r.user_id) AS retained_users,
       ROUND(100.0 * COUNT(r.user_id) / COUNT(*), 1) AS retention_pct
FROM targets t
LEFT JOIN returns r
 ON r.user_id=t.user_id AND r.activity_day=t.target_day
GROUP BY t.signup_day;

Verified output: signup_day = 2026-09-01; eligible_users = 3; retained_users = 1; retention_pct = 33.3.

The targets CTE creates each user’s return date and excludes incomplete observation periods. The returns CTE reduces events to one row per user and activity date. The left join preserves eligible users with no return. Finally, counting the joined user ID counts only successful matches, while counting all joined rows gives the eligible cohort size.

SQLite’s date-function documentation explains the '+7 days' modifier. Its aggregate documentation distinguishes counting all rows from counting non-null values. Multiplying by 100.0 keeps this calculation in fractional arithmetic.

Three mistakes that change the answer

Joining raw events: user a has two events. Without deduplication, the join produces four rows for the three eligible users. Counting matching event rows then gives 2/4, or 50%, instead of 1/3. Adding DISTINCT only to the numerator does not fix an inflated denominator.

Using an inner join: users b and c have no exact-day match and disappear. The remaining matched sample suggests 100% retention. The question concerns all eligible signups, including people who did not return.

Including today’s target cohort: user d has not had a complete day-7 opportunity at the cutoff. Including that user would mix mature and immature cohorts. Do not label this as a decline in engagement.

Check the boundaries before presenting results

1. Remove user a’s duplicate event. The result must remain 33.3%.
2. Move user b’s return to September 8. Retention should become 66.7%.
3. Advance the completeness cutoff to September 10. The September 2 cohort should appear with one eligible and one retained user.
4. Remove all return events. Mature cohorts should remain with zero retained users.
5. Check that signup IDs are unique and non-null. A duplicated signup record breaks the one-row-per-user assumption even when events are deduplicated.

In production, also check late-arriving events, identity merges, test accounts and changes in qualifying-event instrumentation. A clean query cannot repair a changing measurement definition.

Explain the result in a product interview

A useful answer is: “For users who signed up September 1, one of three returned on exact day 7. I excluded the next cohort because its observation day was incomplete and deduplicated repeat activity. This tiny sample is descriptive; it does not establish that onboarding improved or worsened.”

Then propose a next step: compare mature cohorts with the same definition, show cohort sizes alongside percentages, and segment by acquisition channel only when sample sizes support it. If a paid campaign changes the signup mix, overall retention can move even when each channel is stable. Investigate before attributing the change to a product release.

Common follow-up questions

Is day-7 retention the same as seven-day retention?

Not necessarily. Teams may use that phrase for exact day 7, any return during days 1–7, or a rolling return after day 7. State the definition explicitly in the chart and metric documentation.

Should I average cohort percentages?

For a pooled user-level rate, sum retained users and divide by summed eligible users. An unweighted average gives a tiny cohort the same influence as a large one. Either summary can answer a question, but they answer different questions.

How does this differ from daily active users?

DAU counts qualifying users on a reporting day. Retention follows a defined starting cohort to a later point. Higher DAU can come from new acquisitions without stronger retention.

Continue with SQL joins, aggregation practice and business case studies. For coached practice, review the interview plans. Interview sessions are conducted in English; availability and booking status are shown on the service page.