User Activity for the Past 30 Days II - Problem
Given the Activity table that tracks user activities for a social media website, find the average number of sessions per user for a period of 30 days ending 2019-07-27 (inclusive).
Key requirements:
- Count sessions with at least one activity in the specified time period
- Each session belongs to exactly one user
- Round the result to 2 decimal places
The table may contain duplicate rows and includes various activity types: 'open_session', 'end_session', 'scroll_down', 'send_message'.
Table Schema
Activity
| Column Name | Type | Description |
|---|---|---|
user_id
|
int | ID of the user performing the activity |
session_id
|
int | ID of the session (unique per user) |
activity_date
|
date | Date when the activity occurred |
activity_type
|
enum | Type of activity: 'open_session', 'end_session', 'scroll_down', 'send_message' |
Primary Key: None
Note: Table may have duplicate rows. Each session belongs to exactly one user.
Input & Output
Example 1 — Basic Activity Tracking
Input Table:
| user_id | session_id | activity_date | activity_type |
|---|---|---|---|
| 1 | 1 | 2019-07-20 | open_session |
| 1 | 1 | 2019-07-20 | scroll_down |
| 1 | 2 | 2019-07-21 | open_session |
| 2 | 3 | 2019-07-21 | open_session |
| 2 | 3 | 2019-07-21 | send_message |
| 3 | 4 | 2019-06-25 | open_session |
Output:
| average_sessions_per_user |
|---|
| 1.50 |
💡 Note:
Within the 30-day period (2019-06-28 to 2019-07-27), we have: User 1 has 2 sessions (session 1 and 2), User 2 has 1 session (session 3). User 3's activity is outside the date range. Average = (2 + 1) / 2 = 1.50
Example 2 — Single User
Input Table:
| user_id | session_id | activity_date | activity_type |
|---|---|---|---|
| 1 | 1 | 2019-07-15 | open_session |
| 1 | 1 | 2019-07-15 | scroll_down |
| 1 | 2 | 2019-07-16 | send_message |
Output:
| average_sessions_per_user |
|---|
| 2.00 |
💡 Note:
Only User 1 is active in the date range with 2 distinct sessions. Average = 2 / 1 = 2.00
Constraints
-
1 ≤ user_id ≤ 10000 -
1 ≤ session_id ≤ 10000 -
activity_dateis a valid date -
activity_typeis one of'open_session','end_session','scroll_down','send_message'
Visualization
Tap to expand
Understanding the Visualization
1
Filter
Activities within 30-day period
2
Group
Count distinct sessions per user
3
Average
Calculate overall average
Key Takeaway
🎯 Key Insight: Use nested aggregation to first count distinct sessions per user, then calculate the overall average
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code