Cohort Churn Query | Dataford Interview Questions

Write a query to calculate the churn rate by cohort over the last four quarters.

Use the customers and transactions tables. A customer is active in a quarter when they have at least one completed transaction. Churn is the percentage of customers active in the immediately preceding quarter who are not active in the reporting quarter. Use the latest quarter represented in transactions as the endpoint.

Output

One row per cohort and reporting quarter with prior-quarter activity
Columns: cohort_quarter, reporting_quarter, active_customers_previous_quarter, churned_customers, and churn_rate
Order by cohort_quarter, then reporting_quarter

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
signup_date	DATE	Customer signup date used to assign the cohort

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_date	DATE	Date of the transaction
transaction_status	VARCHAR(20)	Transaction outcome, such as completed or cancelled

Your question is Cohort Churn Query. 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