logo

Back to questions

Active User Retention [Facebook SQL Interview Question]

Hard

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

Assume you're given a table containing information on Facebook user actions. Write a query to obtain number of monthly active users (MAUs) in July 2022, including the month in numerical format "1, 2, 3".

Hint:

  • An active user is defined as a user who has performed actions such as 'sign-in', 'like', or 'comment' in both the current month and the previous 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) with the 445.

Please note that the output provided is for June 2022 as the table only contains event dates for that month. You should adapt the solution accordingly for July 2022.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14