Cohort Rolling Conversion Rate | Dataford Interview Questions

GoDaddy wants to monitor conversion performance for customers who begin using products such as Websites and Domains. Write a PostgreSQL query that calculates a 7-day rolling conversion rate for each customer cohort and active event date.

Requirements

Join customer cohort data with funnel events using user_id.
Calculate daily distinct visitors and daily distinct converters for each cohort_date and event_date. A non-null converted_at indicates a conversion.
Use window functions to calculate rolling 7-day visitors and converters within each cohort. The window must include the current date and the previous six calendar days, even when some dates have no activity.
Return the rolling conversion rate, rounded to four decimal places, and order results by cohort and event date.

Only cohort dates with at least one funnel event are required in the output.

Schema

users

Column	Type	Description
user_idPK	INT	Unique customer identifier
cohort_date	DATE	Date the customer entered the cohort
product_name	VARCHAR(100)	GoDaddy product associated with the customer

funnel_events

Column	Type	Description
event_idPK	INT	Unique funnel event identifier
user_id	INT	Customer who generated the event
event_date	DATE	Date of funnel activity
converted_at	TIMESTAMP	Conversion timestamp, or NULL when the event did not convert

Your question is Cohort Rolling Conversion Rate. 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