This is the same question as problem #31 in the SQL Chapter of Ace the Data Science Interview!
Assume you are given the table below containing information on Facebook user logins. Write a query to obtain the number of reactivated users (which are dormant users who did not log in the previous month, then logged in during the current month).
Output the current month (in numerical) and number of reactivated users.
Assume that the above table is complete for month of February to April 2022.
In March 2022, there are 3 reactivated users which are 725, 245, and 112. These 3 users did not login in February 2022, but login in the current month in March 2022.
There are no reactivated users in April because users 245 and 112 login in the previous month in March 2022 and the current month in April, thus they are not reactivated users.
Before we proceed with the solution, let's understand what is a Reactivated User.
Reactivated users are dormant users who did not log in the previous month, who then logged in the following month.
We'll use a multi-step approach to solve this:
In the first step, we look at all the users who login during the previous month. To obtain the last month’s data, we subtract an of 1 month from the current month’s login date.
Then, we use a against previous month’s login information to check whether there was a login in the previous month. If there is a login in the previous month, then the operator excludes this particular user.
Finally, we the number of users satisfying this condition.