User Activity for the Past 30 Days II - Problem

You're analyzing user engagement data for a social media platform. Given an Activity table that tracks user sessions and activities, your task is to calculate the average number of sessions per user over a specific 30-day period.

📊 Table Schema: Activity

Column NameTypeDescription
user_idintUnique identifier for each user
session_idintUnique identifier for each session
activity_datedateDate when the activity occurred
activity_typeenum'open_session', 'end_session', 'scroll_down', 'send_message'

🎯 Your Mission

Calculate the average number of sessions per user for the 30-day period ending on 2019-07-27 (inclusive). The result should be rounded to 2 decimal places.

Key Points:

  • 🔍 Only count sessions that have at least one activity in the target date range
  • 📅 Date range: 2019-06-28 to 2019-07-27 (30 days inclusive)
  • 👥 Each session belongs to exactly one user
  • 📊 Return format: Single column with the average rounded to 2 decimal places

Input & Output

example_1.sql — Basic Case
$ Input: Activity table: | 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 | | 3 | 2 | 2019-07-21 | open_session | | 3 | 2 | 2019-07-21 | send_message | | 3 | 3 | 2019-06-25 | open_session | | 4 | 6 | 2019-06-25 | open_session |
› Output: | average_sessions_per_user | |---------------------------| | 1.33 |
💡 Note: In the 30-day period (2019-06-28 to 2019-07-27), we have: User 1 (1 session), User 2 (1 session), User 3 (1 session - session 3 is outside date range). User 4's session is also outside the range. Average = (1+1+1)/3 = 1.33
example_2.sql — Multiple Sessions
$ Input: Activity table: | user_id | session_id | activity_date | activity_type | |---------|------------|---------------|---------------| | 1 | 1 | 2019-07-01 | open_session | | 1 | 2 | 2019-07-15 | open_session | | 1 | 3 | 2019-07-25 | send_message | | 2 | 4 | 2019-07-10 | open_session | | 2 | 5 | 2019-07-20 | scroll_down |
› Output: | average_sessions_per_user | |---------------------------| | 2.50 |
💡 Note: User 1 has 3 sessions (1, 2, 3) and User 2 has 2 sessions (4, 5). All activities are within the date range. Average = (3+2)/2 = 2.50
example_3.sql — Edge Case - Single User
$ Input: Activity table: | user_id | session_id | activity_date | activity_type | |---------|------------|---------------|---------------| | 1 | 1 | 2019-07-27 | open_session | | 1 | 1 | 2019-07-27 | end_session | | 1 | 2 | 2019-06-28 | send_message |
› Output: | average_sessions_per_user | |---------------------------| | 2.00 |
💡 Note: Only User 1 exists with 2 sessions (1 and 2) in the date range. Both 2019-06-28 and 2019-07-27 are inclusive boundaries. Average = 2/1 = 2.00

Visualization

Tap to expand
User Session Analytics Process📅 Step 1: FilterDate Range Filter2019-06-28 to 2019-07-27Include only activitiesin this 30-day window👥 Step 2: GroupCount Sessions per UserGROUP BY user_idCOUNT(DISTINCT session_id)for each user🧮 Step 3: AverageCalculate Final ResultAVG(session_counts)ROUND(..., 2)Return average📋 Example CalculationInput Data:User 1: Sessions [1, 2, 3] → 3 sessionsUser 2: Sessions [4, 5] → 2 sessionsUser 3: Sessions [6] → 1 sessionCalculation:AVG(3, 2, 1) = 6 ÷ 3 = 2.00ROUND(2.00, 2) = 2.00💡 Key: Count DISTINCT sessions per user, then average across all users
Understanding the Visualization
1
Filter by Date Range
Select only activities within the 30-day window (2019-06-28 to 2019-07-27)
2
Group by Users
Group activities by user_id and count distinct session_id for each user
3
Calculate Average
Take the average of session counts across all users and round to 2 decimal places
Key Takeaway
🎯 Key Insight: The trick is to count DISTINCT sessions per user (not total activities), then calculate the average across users. Use GROUP BY for efficient aggregation in a single query pass.

Time & Space Complexity

Time Complexity
⏱️
O(n)

Single scan through the activity table with efficient grouping

n
2n
✓ Linear Growth
Space Complexity
O(u)

Space proportional to number of unique users for grouping

n
2n
✓ Linear Space

Related Problems

Constraints

  • 1 ≤ Activity table rows ≤ 104
  • 1 ≤ user_id, session_id ≤ 100
  • Date range is exactly 30 days: 2019-06-28 to 2019-07-27 (inclusive)
  • activity_type ∈ {'open_session', 'end_session', 'scroll_down', 'send_message'}
  • Result must be rounded to exactly 2 decimal places
Asked in
Facebook 45 Google 38 Amazon 32 Microsoft 28
38.2K Views
Medium Frequency
~12 min Avg. Time
1.5K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen