Rolling Retention and Drop-Off | Dataford Interview Questions

Intuit wants to evaluate user retention and identify where customers drop out of a product funnel. Write a PostgreSQL query using the users and product_events tables.

Requirements

Group users by the month of signup_date.
Calculate rolling retention rates for activity within 7, 14, and 30 days after signup. Use the signup cohort as the denominator.
For each cohort, report users reaching connect_account, create_budget, and view_insights within 30 days.
Calculate each funnel step's conversion rate from the cohort and its drop-off rate from the immediately preceding step. Return the largest drop-off steps first within each cohort.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
acquisition_channel	VARCHAR(30)	Marketing channel associated with signup

product_events

Column	Type	Description
event_idPK	INT	Unique product event identifier
user_id	INT	User associated with the event
event_name	VARCHAR(50)	Name of the product event
occurred_at	TIMESTAMP	Timestamp when the event occurred

Your question is Rolling Retention and Drop-Off. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem