Thirty-Day Active Session Average

Medium

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 1

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 2

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 3

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.

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

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 |

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 |

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 |