Back to questions
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".
|event_type||string ("sign-in, "like", "comment")|
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!
In order to calculate the active user retention, we need to identify users who were active in both the current month and the previous month. We can approach this in two steps:
To accomplish this, we can use the following code:
Important: Please note that you won't be able to run this query on its own, as it references another table which is not included in this query. We will provide you with the complete query to run later. For now, focus on understanding the logic behind the solution."
To accomplish this, we use the operator to check for users in the current month who also exist in the subquery, which represents active users in the previous month (identified in Step 1). Note that the table is aliased as to indicate that this represents the current month's user information.
To extract the month information from the column, we use the function. Then, we use a over the to obtain the count of monthly active users for the current month.
Here's the complete query to run in the editor:
In July 2022, there are only 2 distinct users who are active in both the previous month and the current month.