Katkat
Back

Build 30-day retention cohort table

IntuitData Scientist · Technical Screen
MediumData ScientistTechnical ScreenAnalytics & Experimentation

Last updated

This problem tests your ability to aggregate user activity data to calculate 30-day retention rates, requiring joins, date arithmetic, and windowing functions.

Retention Cohort Analysis

You are given two tables representing user activity on an Intuit financial application:

  1. users table: user_id, signup_date (Date)
  2. activities table: user_id, activity_date (Date)

Define the '30-day retention' for a daily cohort (users who signed up on a specific date) as the percentage of users who performed at least one activity exactly 30 days after their signup_date.

Write a SQL query to generate a table showing:

  • signup_date
  • cohort_size (total users who signed up that day)
  • retained_users (users who performed an activity exactly 30 days later)
  • retention_rate (percentage of retained users)

Assumptions

  • An activity on day 30 is defined as any record in the activities table where activity_date = signup_date + INTERVAL '30 days'.
  • A user is only counted once per signup date cohort.

Example Input

users table:

user_idsignup_date
12023-01-01
22023-01-01

activities table:

user_idactivity_date
12023-01-31

Expected Output

signup_datecohort_sizeretained_usersretention_rate
2023-01-01210.50

Constraints

  • Ensure you handle cases where a cohort might have zero retained users.
  • Assume signup_date is the primary partitioning key.

Hints

Hint 1

First, create a daily cohort count by grouping the users table by signup_date.

Hint 2

Perform a LEFT JOIN between the users table and the activities table using both user_id and a date filter condition (activity_date = signup_date + 30).

Hint 3

Calculate the retention rate by dividing the count of matched activities by the total count of users in that cohort, ensuring you handle potential division by zero.

Follow-up questions

  • How would you modify this query to calculate 'rolling' retention, where we look for activity on ANY day within the 30-day window rather than exactly on day 30?
  • If the dataset grows to billions of rows, what indexing strategy would you propose to keep this query performant?
  • How would you account for users who have signed up in the last 29 days, as they haven't had the chance to be 'retained' yet?