Rolling Thirty-Day Daily Active Users Report

EASY10 pts

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:
1. day
2. 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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

activity =

[{"user_id":1,"session_id":1,"activity_date":"2019-07-20","activity_type":"open_session"},{"user_id":1,"session_id":1,"activity_date":"2019-07-20","activity_type":"scroll_down"},{"user_id":1,"session_id":1,"activity_date":"2019-07-20","activity_type":"end_session"},{"user_id":2,"session_id":4,"activity_date":"2019-07-20","activity_type":"open_session"},{"user_id":2,"session_id":4,"activity_date":"2019-07-21","activity_type":"send_message"},{"user_id":2,"session_id":4,"activity_date":"2019-07-21","activity_type":"end_session"},{"user_id":3,"session_id":2,"activity_date":"2019-07-21","activity_type":"open_session"},{"user_id":3,"session_id":2,"activity_date":"2019-07-21","activity_type":"send_message"},{"user_id":3,"session_id":2,"activity_date":"2019-07-21","activity_type":"end_session"},{"user_id":4,"session_id":3,"activity_date":"2019-06-25","activity_type":"open_session"},{"user_id":4,"session_id":3,"activity_date":"2019-06-25","activity_type":"end_session"}]

Rolling Thirty-Day Daily Active Users Report

Hints

Rolling Thirty-Day Daily Active Users Report

Hints