# The Seventh Day > Day one was promising. Day seven tells the truth. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem A growth team measures how well each monthly signup cohort sticks around: the share of a month's new users who come back to do something a week or more after joining. Report each cohort month, its total signups, how many of those users came back that late, and that share as a percentage. ## Worked solution and explanation ### What this problem really is This is a per-cohort set difference wearing a growth-metric costume. Anyone can bucket signups by month and count who came back. The tell that separates candidates is what 'came back' means: it is not any visit, it is a visit a week or more out. Plenty of new users bounce back in the first day or two and never mature into real retention, so a query that credits any page view inflates the number and misses the whole point. The second trap is keeping the empty cohorts alive through an outer join, so a month where nobody returned still reports 0 instead of vanishing from the result entirely. --- ### Break down the requirements #### Step 1: Bucket signups by month with strftime strftime('%Y-%m', signup_date) produces labels like '2024-04' that sort chronologically. This is the cohort key. Use strftime, not DATE_TRUNC or TO_CHAR, because the sandbox is SQLite. #### Step 2: Isolate the late returners with a filtered subquery In a subquery, join page_views back to users and keep only the rows where julianday(pv.viewed_at) - julianday(s.signup_date) >= 7 with a WHERE clause, then SELECT DISTINCT user_id. That set is exactly the users whose visit lands a week or more after signup; anyone whose only visits fall inside the first week never appears. Use julianday differences, not DATEDIFF, EXTRACT(EPOCH), or INTERVAL arithmetic, since the sandbox is SQLite. #### Step 3: Keep empty cohorts and compute the percent LEFT JOIN the retained set to users on user_id so every cohort survives even when nobody came back late. COUNT(DISTINCT u.user_id) is the denominator; COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN u.user_id END) is the numerator. CAST one side to DOUBLE before dividing so you get a real fraction, multiply by 100, and ROUND to one decimal. --- ### The solution **Filtered retained set, LEFT JOIN, conditional distinct count** ```sql SELECT strftime('%Y-%m', u.signup_date) AS cohort_month, COUNT(DISTINCT u.user_id) AS total_signups, COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN u.user_id END) AS retained_users, ROUND(CAST(COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN u.user_id END) AS DOUBLE) / COUNT(DISTINCT u.user_id) * 100, 1) AS retention_pct FROM users u LEFT JOIN ( SELECT DISTINCT pv.user_id FROM page_views pv JOIN users s ON s.user_id = pv.user_id WHERE julianday(pv.viewed_at) - julianday(s.signup_date) >= 7 ) r ON u.user_id = r.user_id GROUP BY cohort_month ORDER BY cohort_month ``` > **Cost Analysis** > > users is 10M rows, page_views is 500M. The retained subquery filters and dedups on page_views(user_id, viewed_at); an index there makes it a streaming scan. The COUNT(DISTINCT) is the expensive part because SQLite sorts to dedupe; if you can guarantee one row per user in the retained set, the outer DISTINCT collapses to a cheap presence check. > **Interviewers Watch For** > > Did you gate retention on the seven-day gap in the WHERE filter rather than crediting any visit, use strftime('%Y-%m', ...) for the cohort key, julianday differences (not DATEDIFF, EXTRACT(EPOCH), or INTERVAL), CAST to DOUBLE before the percent division, and LEFT JOIN the retained set so cohorts with zero returners still appear? Candidates who INNER JOIN at the top level drop empty cohorts silently. > **Common Pitfall** > > Two pitfalls collide here: forgetting to CAST one side of the division to DOUBLE gives integer division and you get 0 for almost every cohort; and treating anyone with a page view as retained double-counts the early bouncers, since the prompt says a week or more after joining, so the day-gap filter is load-bearing. --- ## Common follow-up questions - How would you compute Day-N retention for arbitrary N? _(Parameterize the 7 in the WHERE condition. 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 users with zero page_views? _(The LEFT JOIN keeps them; they never appear in the retained set, so r.user_id is NULL and they do not contribute to retained_users. Their cohort still gets credit in total_signups, which is the correct denominator.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_seventh_day) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.