SQLTeam.com | Weblogs | Forums

Calculate retention with SQL

Having a table containing UserID and dates only:

ID | Date

111 | 01/01/2021
222 | 30/01/2021
333 | 22/01/2021

Desired output would be (active users are users that have an activity in the past 30 days, retained users are users who were active before that ):

Date | Number_of_Active_users | Number_of_Retained_users | Retention%

01/01/2021 | 3 | 2 | 22%
03/01/2021 | 8 | 4 | 60%

Thanks

welcome!

definitely some missing data. what you have does not seem to be the whole truth

Thanks!
It is one table that retention should be extracted from.
There are no other tables.

What is a retained user? How is the retention percent calculated? What have you tried?