Active User Stickiness
ASKED IN INTERVIEWMeta
Problem Statement
Meta uses the "Stickiness" metric to understand user retention. Stickiness is the ratio of Daily Active Users (DAU) to Monthly Active Users (MAU).
Write an SQL query to calculate the stickiness for the month of July 2022.
Rules:
- DAU (Daily Active Users): The average number of unique users active per day throughout the month.
- MAU (Monthly Active Users): The total number of unique users active at least once during the entire month.
- An "active" user is defined as any user found in the user_actions table for that period.
- The result should be a single column named stickiness, rounded to 4 decimal places.
- Only consider data from July 2022.
Tests your understanding of
Metrics, Ratio, Dates and Engagement
Input Tables
user_actions
| user_id(INTEGER) | event_id(INTEGER) | event_type(VARCHAR) | event_date(TIMESTAMP) |
|---|---|---|---|
| 1 | 101 | post | 2022-07-01 10:00:00 |
| 1 | 102 | like | 2022-07-02 11:00:00 |
| 2 | 103 | post | 2022-07-01 12:00:00 |
| 2 | 104 | post | 2022-07-01 13:00:00 |
| 3 | 105 | like | 2022-07-05 09:00:00 |
| 4 | 106 | login | 2022-07-10 14:00:00 |
| 1 | 107 | post | 2022-07-10 15:00:00 |
| 5 | 108 | post | 2022-07-20 10:00:00 |
| 2 | 109 | like | 2022-07-20 11:00:00 |
| 1 | 110 | post | 2022-07-31 23:00:00 |
| 6 | 111 | login | 2022-06-30 23:00:00 |
| 7 | 112 | login | 2022-08-01 01:00:00 |
user_profiles
| user_id(INTEGER) | account_type(VARCHAR) |
|---|---|
| 1 | Personal |
| 2 | Business |
activity_logs
| log_id(INTEGER) | user_id(INTEGER) | session_duration(INTEGER) |
|---|---|---|
| 1 | 1 | 300 |
Expected Output
| stickiness(DECIMAL) |
|---|
| 0.3 |
Tags
MediumASKED IN INTERVIEWMetricsRatioDatesEngagement
20-25 min
48%