logo

Back to questions

Reactivated Users

Hard

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.

Assumption:

  • The rows in user_logins table is complete for the year of 2022 and there are no missing dates.

Table:

Column NameType
user_idinteger
login_datedatetime

Table:

user_idlogin_date
72503/03/2022 12:00:00
24503/28/2022 12:00:00
11203/05/2022 12:00:00
24504/29/2022 12:00:00
11204/05/2022 12:00:00

Assume that the above table is complete for month of February to April 2022.

Example Output:

current_monthreactivated_users
33

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.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.