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_date is a valid date
  • activity_type is one of 'open_session', 'end_session', 'scroll_down', 'send_message'

Visualization

Tap to expand
User Activity for Past 30 Days II INPUT Activity Table user_id session date activity 1 1 07-20 open 1 1 07-20 scroll 1 2 07-21 like 2 3 07-21 open 3 4 06-25 end Date Range Filter 2019-06-28 to 2019-07-27 (30 days inclusive) Sample Data Summary Users: 1, 2, 3 Sessions: 1, 2, 3, 4 Activities: open, scroll, like... Row 5: Outside date range ALGORITHM STEPS 1 Filter by Date WHERE date BETWEEN '2019-06-28' AND '2019-07-27' 2 Count Distinct Sessions COUNT(DISTINCT session_id) Sessions = 3 (valid) 3 Count Distinct Users COUNT(DISTINCT user_id) Users = 2 (in range) 4 Calculate Average sessions / users ROUND(3/2, 2) = 1.50 SQL Query SELECT ROUND( COUNT(DISTINCT session_id)/ COUNT(DISTINCT user_id), 2) FROM Activity WHERE date... FINAL RESULT Average Sessions/User 1.50 Calculation Total Sessions: 3 Total Users: 2 3 / 2 = 1.50 Output average_sessions_per_user 1.50 Key Insight: The solution uses IFNULL to handle edge cases where no users exist in the date range (returns NULL instead of error). DATEDIFF calculates days between dates. Sessions are counted only once per user even with multiple activities. ROUND(..., 2) ensures exactly 2 decimal places in the output as required. TutorialsPoint - User Activity for the Past 30 Days II | Optimal Solution

Related Problems

Asked in
Facebook 28 Amazon 22 Google 18
34.5K Views
Medium Frequency
~12 min Avg. Time
890 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