Katkat
Back

Count Users Retained on Day Seven

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

Last updated

Count users active on the seventh calendar day after signup, without double-counting repeated events.

Count Users Retained on Day Seven

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.

Write one read-only PostgreSQL query to count distinct users with at least one event on the calendar date seven days after their signup calendar date. Match events to users by user_id.

Compare the date components of event_ts and signup_date: time of day does not change whether an event is on day seven. This asks about the seventh calendar day, rather than any event during the first seven days. Count each qualifying user once even if that user has several events on that date.

Return exactly one row with one column named d7_retained_users. If nobody qualifies, the count is zero. Output row order is unrestricted.