0

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.

Dmitry
  • 499
  • 1
  • 3
  • 19

1 Answers1

1
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.

Akina
  • 39,301
  • 5
  • 14
  • 25
  • I think code looks correct, but there are 2 questions: – Dmitry Dec 15 '21 at 11:19
  • 1) if users registered within year is 0 - we will get division by zero and SQL error? 2) can you please add group by user type to your SQL? – Dmitry Dec 15 '21 at 11:21
  • 1 - I think this will never happen because you get only years list where users was registered 2 - I found how to do this. Thanks your code works good as I see. – Dmitry Dec 15 '21 at 11:30