User Activity for the Past 30 Days I
Meta
Problem Statement
Product managers at Meta track Daily Active Users (DAU) to measure platform health. You need to analyze user engagement for a specific 30-day period.
Write an SQL query to find the daily active user count for a period of 30 days ending 2019-07-27 inclusively. A user was active on a given day if they performed at least one activity.
Rules:
- The 30-day period starts on 2019-06-28 and ends on 2019-07-27.
- Return the result table with columns day and active_users.
- Do not include days with zero active users.
- Results must be sorted by day in ascending order.
Tests your understanding of
Basic SQL, Aggregation, Date Functions and DATEDIFF
Input Tables
activity
| user_id(INTEGER) | session_id(INTEGER) | activity_date(DATE) | activity_type(ENUM('open_session', 'end_session', 'scroll_down', 'send_message')) |
|---|---|---|---|
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 1 | 2019-07-20 | end_session |
| 2 | 4 | 2019-07-20 | open_session |
| 2 | 4 | 2019-07-21 | send_message |
| 2 | 4 | 2019-07-21 | end_session |
| 3 | 2 | 2019-07-21 | open_session |
| 3 | 2 | 2019-07-21 | send_message |
| 4 | 3 | 2019-06-25 | open_session |
Expected Output
| day(DATE) | active_users(INTEGER) |
|---|---|
| 2019-07-20 | 2 |
| 2019-07-21 | 2 |
Tags
EasyBasic SQLAggregationDate FunctionsDATEDIFF
10-15 min
52%