Thirty-Day Active Session Average

MEDIUM20 pts

A social product analytics system stores event logs in one table:

activity columns:

user_id: account identifier
session_id: browsing/chat/app session identifier
activity_date: event date in YYYY-MM-DD format
activity_type: category of event (open_session, end_session, scroll_down, send_message)

Data notes:

The same session can emit many activity rows.
Duplicate rows may exist due to reprocessing.
Each session_id belongs to exactly one user_id.

Task:

Use the inclusive 30-day period ending on 2019-07-27.
The effective window is [2019-06-28, 2019-07-27].
For each user active in this window, count distinct session_id values observed for that user in the window.
Compute the average of those per-user session counts.
Round to 2 decimal places.
If no users are active in the window, return 0.00.

Output requirements:

Return exactly one row
Return exactly one column: average_sessions_per_user

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

activity:
| user_id | session_id | activity_date | activity_type |
|---------|------------|---------------|---------------|
| 10      | 1001       | 2019-07-20    | open_session  |
| 10      | 1001       | 2019-07-20    | scroll_down   |
| 10      | 1001       | 2019-07-20    | end_session   |
| 20      | 2001       | 2019-07-21    | open_session  |
| 20      | 2001       | 2019-07-21    | end_session   |
| 30      | 3001       | 2019-07-05    | open_session  |
| 30      | 3002       | 2019-07-26    | open_session  |
| 40      | 4001       | 2019-05-12    | open_session  |

Output

[
  {"average_sessions_per_user":1.33}
]

Explanation

Active users in the window are 10, 20, and 30 with session counts 1, 1, and 2. Their average is (1 + 1 + 2) / 3 = 1.33.

Example

Input

activity:
| user_id | session_id | activity_date | activity_type |
|---------|------------|---------------|---------------|
| 1       | 11         | 2019-05-01    | open_session  |
| 2       | 22         | 2019-08-01    | open_session  |
| 2       | 22         | 2019-08-01    | end_session   |

Output

[
  {"average_sessions_per_user":0.0}
]

Explanation

No row falls inside [2019-06-28, 2019-07-27], so there are no active users and the result is 0.00.

Example

Input

activity:
| user_id | session_id | activity_date | activity_type |
|---------|------------|---------------|---------------|
| 7       | 701        | 2019-06-28    | open_session  |
| 7       | 701        | 2019-06-15    | send_message  |
| 7       | 702        | 2019-07-27    | open_session  |
| 7       | 702        | 2019-07-27    | open_session  |
| 8       | 801        | 2019-06-01    | open_session  |
| 9       | 901        | 2019-07-10    | open_session  |
| 9       | 901        | 2019-07-10    | scroll_down   |

Output

[
  {"average_sessions_per_user":1.5}
]

Explanation

User 7 has two distinct in-window sessions (701 and 702), user 9 has one, and user 8 is inactive in-window. Average = (2 + 1) / 2 = 1.50.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in activity <= 200000
1 <= user_id, session_id <= 1000000000
activity_date is a valid date in YYYY-MM-DD format
activity_type is one of: open_session, end_session, scroll_down, send_message
The table may contain duplicate rows
Each session_id belongs to exactly one user_id
Count distinct session_id values per user inside the target window
A user contributes to the average only if active in the target window
Round final output to exactly two decimal places
If no users are active in the window, return 0.00
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with one column: average_sessions_per_user

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

activity =

[{"user_id":10,"session_id":1001,"activity_date":"2019-07-20","activity_type":"open_session"},{"user_id":10,"session_id":1001,"activity_date":"2019-07-20","activity_type":"scroll_down"},{"user_id":10,"session_id":1001,"activity_date":"2019-07-20","activity_type":"end_session"},{"user_id":20,"session_id":2001,"activity_date":"2019-07-21","activity_type":"open_session"},{"user_id":20,"session_id":2001,"activity_date":"2019-07-21","activity_type":"end_session"},{"user_id":30,"session_id":3001,"activity_date":"2019-07-05","activity_type":"open_session"},{"user_id":30,"session_id":3002,"activity_date":"2019-07-26","activity_type":"open_session"},{"user_id":40,"session_id":4001,"activity_date":"2019-05-12","activity_type":"open_session"}]

Thirty-Day Active Session Average

Hints

Thirty-Day Active Session Average

Hints