Get users churn rate grouped by years in SQL request-CodePudding

I need to get users churn rate value grouped by years and user types in single SQL request.

Churn rate = Users lost this year (archived) / (Users count at start of this year Users count registered in this year)

Users lost this year - users who have 'archive_date' (DATETIME) field within specific year.

Users counts - can be calculated by 'registered' (DATETIME) field.

I need to get this data grouped by all years that exists in DB (for user registration dates), and grouped by user type.

Expected results (just a sample):

year  |  user_type  |  churn_rate
2019  |  A          |  32
2019  |  B          |  20
2019  |  C          |  15
2020  |  A          |  52
2020  |  B          |  45
... etc years and user types

Sample data DB fiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=0f79c8fb40b6c1185908f91ce39d6251

For example for this sample fiddle in 2020 year registered 1 new user, we have 2 users at start of 2020 (they are registered in 2019), and we have 1 user archived in 2020, so (if we will not group this by user type) overall churn rate for 2020 year will be:

Churn rate = 1 / (2 1) = 0.33 (33% Churn rate)

Code should work with MySQL 5.7.

CodePudding user response：

SELECT year_reg,
       SUM(YEAR(registered) < year_reg AND (YEAR(archive_date) > year_reg OR archive_date IS NULL)) AS reg_before,
       SUM(YEAR(registered) = year_reg) AS reg_cur,
       SUM(YEAR(archive_date) = year_reg) AS arch_cur,
       SUM(YEAR(archive_date) = year_reg) / (SUM(YEAR(registered) < year_reg AND (YEAR(archive_date) > year_reg OR archive_date IS NULL)) / SUM(YEAR(registered) = year_reg)) churn_rate
FROM users
CROSS JOIN (SELECT DISTINCT YEAR(registered) year_reg
            FROM users) years_list
GROUP BY year_reg

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=7c289b8f88c1fe57870229965615d97c

PS. I'm not sure that your formula for Churn rate (%) is correct... so I return separate statistic values. Combine them till correct output.