Calculate Churn rate using company level table with subscriptiondate and terminationdate?
Asked in the 1:1 technical stage. The table is company(companyid, signupdate, subscriptiondate, terminationdate), and churn rate is customers that churned in a period divided by total customers at the start of that period.
period_month, churned_customers, customers_at_start, and churn_rate.period_month ascending.subscriptiondate is on or before period_month and terminationdate is NULL or on or after it.period_month through the exclusive first day of the following month.subscriptiondate and latest non-NULL terminationdate to determine the final month.churn_rate to 4 decimal places and return NULL when customers_at_start is zero.| Column | Type | Description |
|---|---|---|
| companyidPK | INT | Unique company identifier |
| signupdate | DATE | Date the company signed up |
| subscriptiondate | DATE | Date the company subscription began |
| terminationdate | DATE | Date the company subscription terminated |