Describe the SQL and data model for weekly cohort retention

WHAT THIS TESTS: This question tests whether you can model time-bound user behavior relationally and avoid the classic trap of aggregate retention. The interviewer cares that you understand a user belongs to exactly one signup cohort, and that every later activity must be measured relative to that specific origin point. They also want to see that you know how to handle calendar weeks, distinct user counting, and denominator management in SQL.

A GOOD ANSWER COVERS: A strong answer describes two core tables. First, a users table with at least user_id and signup_date or signup_week. Second, an activity table with user_id and activity_date. The query should join users to activity on user_id, then compute a week difference using a formula like datediff week or truncating both dates to week start. The result is grouped by signup_week and week_offset, counting distinct user_ids in the numerator. The denominator is the total distinct users in each signup_week cohort. Finally, the answer should mention pivoting the result either with conditional aggregation like sum case when week_offset equals one then one end or with a crosstab presentation so that columns represent week two, week three, and week four retention.

COMMON WRONG ANSWERS: The biggest red flag is computing global active users per week and calling that retention. Another error is using calendar weeks for both signup and activity without normalizing to a common week start, which creates boundary mismatches. Some candidates forget to count distinct users and instead count activity rows, inflating retention when power users return many times. A subtle mistake is including users who signed up mid-week in a weekly cohort without deciding whether to use strict seven-day rolling windows or calendar weeks, because the two definitions produce different percentages.

LIKELY FOLLOW-UPS: The interviewer may ask how you would handle missing weeks where a user returns after a gap, and whether you count that as retained in the skipped weeks. They might ask how to adapt the query for daily or monthly granularity, or how to add an experiment dimension so you can compare retention between test and control groups. Another common extension is asking how you would optimize the query when the activity table has billions of rows, which leads to partitioning and indexing strategies.

ONE CONCRETE EXAMPLE: Imagine a users table where user one signs up on January first and user two signs up on January eighth. The activity table shows user one active on January first, eighth, and fifteenth, while user two is active only on January eighth. Using calendar weeks, both users belong to distinct signup cohorts. The week zero retention for the January first cohort is one hundred percent because user one was active that week. The week one retention is also one hundred percent because user one returned the week of January eighth. The January eighth cohort has one hundred percent week zero retention but zero percent week one retention if user two does not return. The SQL would express this by truncating dates to week, joining, and grouping.

Source: StrataScratch