The Seventh Day

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

A growth team measures how well each monthly signup cohort sticks around, looking only at accounts that cleared verification. For each signup month report how many users joined, how many of them came back to view a page a week or more after signing up, and that return count as a percentage of the month's signups, earliest cohort first.

Day one was promising. Day seven tells the truth.

Tables

users

Columns: user_id INTEGER, signup_date TEXT, username TEXT, email TEXT, account_status TEXT, age_bucket TEXT

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

Worked solution and explanation

What this problem really is

This is a per-cohort retention rate with two traps hiding under a friendly growth surface. First, 'came back' is not any visit: it is a visit a week or more after signup, so crediting any page view counts the early bouncers who never matured and inflates the number. Second, once you join each signup to its page views, one user fans out into one row per visit (some users carry sixty-plus at seed scale), so a plain COUNT of rows balloons both the signup denominator and the returner numerator and the rate sails past 100 percent. The move that separates candidates is COUNT(DISTINCT user_id) on both counts to collapse that fan-out, the seven-day gate expressed as a CASE inside the returner count so the join stays a plain equi-join, and a LEFT JOIN so a month where nobody returned still reports 0 instead of vanishing. The population is scoped to accounts that cleared verification, which is a filter on the users side, so it must never touch the page_views side or it would quietly turn the outer join back into an inner one.

Break down the requirements

Step 1: Bucket signups by month with strftime

strftime('%Y-%m', signup_date) produces labels like '2024-07' that sort chronologically. This is the cohort key. Use strftime, not DATE_TRUNC or TO_CHAR, because the sandbox is SQLite.

Step 2: Attach all activity via a plain equi-join

LEFT JOIN page_views to users on user_id alone. Every one of a user's visits attaches, and because it is a LEFT JOIN, users with no page views at all survive with NULL on the page_views side. Keeping the join a plain equi-join, with no day-gap in the ON, is exactly what makes the fan-out real, so the DISTINCT in the next step is load-bearing rather than decorative.

Step 3: Scope to verified accounts without breaking the outer join

Scope the population with WHERE u.account_status <> 'pending_verification'. Keep this predicate on the users side only: filter the right (page_views) side and the outer join collapses, dropping every cohort where nobody returned. Because it touches only users, the zero-return cohorts still survive.

Step 4: Gate retention in a conditional distinct count and compute the percent

The seven-day gate lives in a CASE inside the count: COUNT(DISTINCT CASE WHEN julianday(pv.viewed_at) - julianday(u.signup_date) >= 7 THEN u.user_id END). Only visits a week or more after signup are credited, and a user with several qualifying visits still counts once. The denominator is COUNT(DISTINCT u.user_id). CAST one side to DOUBLE before dividing so you get a real fraction, multiply by 100, ROUND to one decimal, and ORDER BY cohort_month so the earliest month comes first. Use julianday differences, not DATEDIFF, EXTRACT(EPOCH), or INTERVAL arithmetic, since the sandbox is SQLite.

The solution

SELECT strftime('%Y-%m', u.signup_date) AS cohort_month,
       COUNT(DISTINCT u.user_id) AS total_signups,
       COUNT(DISTINCT CASE WHEN julianday(pv.viewed_at) - julianday(u.signup_date) >= 7 THEN u.user_id END) AS retained_users,
       ROUND(CAST(COUNT(DISTINCT CASE WHEN julianday(pv.viewed_at) - julianday(u.signup_date) >= 7 THEN u.user_id END) AS DOUBLE) / COUNT(DISTINCT u.user_id) * 100, 1) AS retention_pct
FROM users u
LEFT JOIN page_views pv
       ON pv.user_id = u.user_id
WHERE u.account_status <> 'pending_verification'
GROUP BY cohort_month
ORDER BY cohort_month

Common follow-up questions

• How would you compute Day-N retention for arbitrary N? (Parameterize the 7 in the CASE predicate. The structure is identical; you just shift the return boundary.)
• How would you switch to weekly cohorts? (Swap strftime('%Y-%m', ...) for a week format such as strftime('%Y-%W', ...). The retention logic is unchanged; only the cohort key moves.)
• How would you drop cohorts too recent to have had a full week to return? (Add a filter comparing `signup_date` against the current date, keeping only cohorts where every member has already had a full seven days to come back. It is a maturity guard on the denominator so recent months don't read artificially low.)
• How do you handle verified users with zero `page_views`? (The LEFT JOIN keeps them; their only rows have pv NULL (or none qualify), so the CASE is never true and they do not contribute to `retained_users`. Their cohort still gets credit in `total_signups`, which is the correct denominator.)