Rolling Thirty-Day Daily Active Users Report

Easy

You are given a social platform event table named Activity. Table columns: user_id: identifier for a user account session_id: identifier for a session activity_date: event date in YYYY-MM-DD format activity_type: one of ('open_session', 'end_session', 'scroll_down', 'send_message') Notes: The table may contain duplicate rows. A session_id belongs to exactly one user_id. Definition: A user is active on a day if that user has at least one activity row on that date. Task: Compute daily active user counts for the inclusive 30-day window ending on 2019-07-27. In other words, include rows where activity_date is in [2019-06-28, 2019-07-27]. Output requirements: Return exactly two columns in this order: day active_users Return one row per day that has at least one active user. Sort by day in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

activity:
| user_id | session_id | activity_date | activity_type |
|---------|------------|---------------|---------------|
| 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  |
| 3       | 2          | 2019-07-21    | end_session   |
| 4       | 3          | 2019-06-25    | open_session  |
| 4       | 3          | 2019-06-25    | end_session   |

Output

[
  {"day":"2019-07-20","active_users":2},
  {"day":"2019-07-21","active_users":2}
]

Explanation

Only events in [2019-06-28, 2019-07-27] are considered. User 4 only appears outside that window.

Example 2

Input

activity:
| user_id | session_id | activity_date | activity_type |
|---------|------------|---------------|---------------|
| 10      | 100        | 2019-06-28    | open_session  |
| 10      | 100        | 2019-06-28    | scroll_down   |
| 10      | 100        | 2019-06-28    | scroll_down   |
| 11      | 110        | 2019-06-27    | open_session  |
| 12      | 120        | 2019-07-27    | send_message  |
| 12      | 120        | 2019-07-27    | end_session   |
| 13      | 130        | 2019-07-28    | open_session  |

Output

[
  {"day":"2019-06-28","active_users":1},
  {"day":"2019-07-27","active_users":1}
]

Explanation

Boundary days are inclusive. Duplicate rows and multiple events from one user still count once per day.

Example 3

Input

activity:
| user_id | session_id | activity_date | activity_type |
|---------|------------|---------------|---------------|
| 20      | 200        | 2019-06-10    | open_session  |
| 21      | 210        | 2019-06-11    | send_message  |
| 22      | 220        | 2019-08-01    | end_session   |

Output

[]

Explanation

No row falls inside the target 30-day interval, so no day is reported.

Constraints

0 <= number of rows in Activity <= 10,000
1 <= user_id, session_id <= 1,000,000,000
activity_type is one of: open_session, end_session, scroll_down, send_message
activity_date is a valid date in YYYY-MM-DD format
Duplicate rows may exist
Each session_id maps to exactly one user_id
Evaluation window is inclusive: [2019-06-28, 2019-07-27]
A user contributes at most one count per day
Return columns exactly as: day, active_users
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: day, active_users

activity: | user_id | session_id | activity_date | activity_type | |---------|------------|---------------|---------------| | 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 | | 3 | 2 | 2019-07-21 | end_session | | 4 | 3 | 2019-06-25 | open_session | | 4 | 3 | 2019-06-25 | end_session |

activity: | user_id | session_id | activity_date | activity_type | |---------|------------|---------------|---------------| | 10 | 100 | 2019-06-28 | open_session | | 10 | 100 | 2019-06-28 | scroll_down | | 10 | 100 | 2019-06-28 | scroll_down | | 11 | 110 | 2019-06-27 | open_session | | 12 | 120 | 2019-07-27 | send_message | | 12 | 120 | 2019-07-27 | end_session | | 13 | 130 | 2019-07-28 | open_session |

activity: | user_id | session_id | activity_date | activity_type | |---------|------------|---------------|---------------| | 20 | 200 | 2019-06-10 | open_session | | 21 | 210 | 2019-06-11 | send_message | | 22 | 220 | 2019-08-01 | end_session |