logo

Back to questions

Active User Retention

Hard

This is the same question as problem #23 in the SQL Chapter of Ace the Data Science Interview!

Assume you have the table below containing information on Facebook user actions. Write a query to obtain the active user retention in July 2022. Output the month (in numerical format 1, 2, 3) and the number of monthly active users (MAUs).

Hint: An active user is a user who has user action ("sign-in", "like", or "comment") in the current month and last month.

Table:

Column NameType
user_idinteger
event_idinteger
event_typestring ("sign-in, "like", "comment")
event_datedatetime

Example Input:

user_idevent_idevent_typeevent_date
4457765sign-in05/31/2022 12:00:00
7426458sign-in06/03/2022 12:00:00
4453634like06/05/2022 12:00:00
7421374comment06/05/2022 12:00:00
6483124like06/18/2022 12:00:00

Example Output for June 2022:

monthmonthly_active_users
61

Example

In June 2022, there was only one monthly active user (MAU), 445.

Note: We are showing you output for June 2022 as the user_actions table only have event_dates in June 2022. You should work out the solution for July 2022.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.