Interview Query

Annual Retention

0
Have you seen this question before?

You’re given a table called annual_payments for an annually billed B2B SAAS subscription product.

annual_payments table

Column Type
id INTEGER
status VARCHAR
user_id INTEGER
created_at DATETIME
amount FLOAT
amount_refunded FLOAT
product VARCHAR
last_updated DATETIME

Users pay for the three different products: 'PDF Editor', 'Cloud Storage', and 'Mobile CRM'.

How would you formulate a query to calculate the average annual retention, for each subsequent year, at the end of the year?

Retention rate is calculated as a percentage of active subscriptions at the end of the year TT in the active subscriptions at the end of the previous year (T1)(T-1).

Example 1:

User 1 bought a subscript to PDF editor in 2019 for the first time and renewed their subscription in 2020. In 2021, they canceled their subscription. Retention rates should be calculated as:

2019 2020 2021
0.00 1.00 0.00

Example 2:

User 2 bought a subscription to 'PDF Editor' in 2019 for the first time, renewed their subscription in 2020, and canceled a few days later in 2020. Retention rates should be calculated as:

2019 2020 2021
0.00 0.00 0.00

Notes:

  1. The status column may contain the values 'paid', 'refunded', or 'failed'.
  2. A 'failed' payment equates to a user canceling their subscription on the date the next payment is due.
  3. If a user refunds a successful payment, then the row gets updated, and the status becomes 'refunded'. The date of refund is recorded in last_updated.
  4. When a refund occurs in year TT, the subscription is canceled. Users may purchase the same product again in the subsequent years, and it is considered a new purchase.
Next question: Distribution of 2X - Y
.....
MySQL 8.0.17
Loading editor
Use Shift + Enter to run query