Katkat
Back

Compare First-Week Retention by Plan Type

MixpanelData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

Compare first-week retention by recorded plan type with distinct-user counts, inclusive denominators, and four-decimal rates.

Compare First-Week Retention Across Plan Types

You have these tables:

TableColumnMeaning
usersuser_idUser identifier.
userssignup_dateThe user's signup date or timestamp.
usersplan_typeThe user's plan type.
eventsuser_idUser associated with an event.
eventsevent_tsEvent timestamp.

For this exercise, a user is retained if the user has at least one matching event whose event_ts is greater than or equal to signup_date and strictly less than signup_date plus seven days. Match events to user records by user_id.

Write one read-only PostgreSQL query that reports first-week retention separately for each plan_type present in users. Return these columns in this order:

  • plan_type: the plan group.
  • users: the number of distinct user identifiers in that plan group.
  • retained_users: the number of distinct users in that group meeting the first-week event condition.
  • retention_rate: retained_users divided by users, rounded to four decimal places.

Include users with no qualifying events in the denominator, and count each retained user once even when several events qualify. Include plan groups with zero retained users. Group by the recorded plan value; do not collapse plan types or filter to a subset of plans.

The output row order is unrestricted.