Window Functions and Churn SQL | Dataford Interview Questions

Explain how you would use window functions to analyze churn rate for Navi’s lending customers.

Use the provided Navi lending customer and monthly activity data. Return one row per activity month, including customers who were active in the previous observed month as the churn denominator.

Output

Columns: activity_month, prior_active_customers, churned_customers, churn_rate_percent
Churn rate is churned customers divided by prior active customers, expressed as a percentage
Sort by activity_month ascending

Schema

lending_customers

Column	Type	Description
customer_idPK	INT	Unique Navi lending customer identifier
customer_name	VARCHAR(100)	Customer name
customer_segment	VARCHAR(30)	Customer lending segment

customer_monthly_activity

Column	Type	Description
activity_idPK	INT	Unique monthly activity record identifier
customer_id	INT	References lending_customers.customer_id
activity_month	DATE	First day of the activity month
is_active	BOOLEAN	Whether the customer was active during the month

Your question is Window Functions and Churn SQL. 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