User Activity for the Past 30 Days I

Difficulty: Easy | Acceptance: 50.80% | Paid: No Topics: Database

Table: Activity +--------------+---------+ | Column Name | Type | +--------------+---------+ | user_id | int | | session_id | int | | activity_date| date | | activity_type| enum | +--------------+---------+ There is no primary key for this table, it may contain duplicates. The activity_type column is an ENUM (type) of (‘open_session’, ‘end_session’, ‘scroll_down’, ‘send_message’). The table contains the user activity logs for a social media platform.

Write a solution to find the daily active user count for a period of 30 days ending on 2019-07-27 inclusive. A user is considered active if they have performed at least one activity during that period.

The result format is in the following example.

• Examples
• Constraints
• Approach 1: GROUP BY with COUNT(DISTINCT)
• Approach 2: Subquery with Date Range
• Approach 3: Using DATEDIFF Function

Examples

Example 1

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   | end_session  |
| 3       | 2          | 2019-07-21   | open_session |
| 3       | 2          | 2019-07-21   | send_message |
| 3       | 2          | 2019-07-21   | end_session  |
| 4       | 3          | 2019-07-21   | open_session |
| 4       | 3          | 2019-07-27   | end_session  |
+---------+------------+--------------+--------------+

Output:

+------------+--------------+
| day        | active_users |
+------------+--------------+
| 2019-07-20 | 2            |
| 2019-07-21 | 3            |
| 2019-07-27 | 1            |
+------------+--------------+

Explanation: Note that we do not care about activity days with no active users.

Constraints

The activity_date will be between 2019-06-28 and 2019-07-27 inclusive.
Each user_id will be an integer between 1 and 100.

GROUP BY with COUNT(DISTINCT)

Intuition Group activities by date and count distinct users for each day within the 30-day window.

Steps

• Filter activities where activity_date is between 2019-06-28 and 2019-07-27 (30 days ending on 2019-07-27)
• Group by activity_date
• Count distinct user_ids for each group
• Order by activity_date

import pandas as pd

def user_activity(activity: pd.DataFrame) -> pd.DataFrame:
    start_date = pd.to_datetime('2019-06-28')
    end_date = pd.to_datetime('2019-07-27')
    
    activity['activity_date'] = pd.to_datetime(activity['activity_date'])
    filtered = activity[(activity['activity_date'] >= start_date) & 
                        (activity['activity_date'] <= end_date)]
    
    result = filtered.groupby('activity_date')['user_id'].nunique().reset_index()
    result.columns = ['day', 'active_users']
    result = result.sort_values('day')
    
    return result

Complexity

• Time: O(n log n) where n is the number of activity records (due to sorting)
• Space: O(n) for storing the daily user sets
• Notes: This is the most straightforward approach and works well for the given constraints.

Subquery with Date Range

Intuition First filter the activities for the 30-day period using a subquery, then aggregate by date.

Steps

• Create a subquery to filter activities within the date range
• Group the filtered results by activity_date
• Count distinct user_ids
• Order by activity_date

import pandas as pd

def user_activity(activity: pd.DataFrame) -> pd.DataFrame:
    start_date = '2019-06-28'
    end_date = '2019-07-27'
    
    filtered = activity[
        (activity['activity_date'] >= start_date) & 
        (activity['activity_date'] <= end_date)
    ]
    
    result = filtered.groupby('activity_date').agg(
        active_users=('user_id', 'nunique')
    ).reset_index()
    
    result.columns = ['day', 'active_users']
    result = result.sort_values('day')
    
    return result

Complexity

• Time: O(n log n) where n is the number of activity records
• Space: O(n) for storing filtered data and daily user sets
• Notes: This approach separates filtering from aggregation, which can be more readable.

Using DATEDIFF Function

Intuition Use date difference functions to filter activities within the 30-day window from the end date.

Steps

• Calculate the date difference between activity_date and the end date (2019-07-27)
• Filter records where the difference is between 0 and 29 days
• Group by activity_date and count distinct user_ids
• Order by activity_date

import pandas as pd
from datetime import datetime, timedelta

def user_activity(activity: pd.DataFrame) -> pd.DataFrame:
    end_date = datetime(2019, 7, 27)
    start_date = end_date - timedelta(days=29)
    
    activity['activity_date'] = pd.to_datetime(activity['activity_date'])
    
    filtered = activity[
        (activity['activity_date'] >= start_date) & 
        (activity['activity_date'] <= end_date)
    ]
    
    result = filtered.groupby('activity_date')['user_id'].nunique().reset_index()
    result.columns = ['day', 'active_users']
    result = result.sort_values('day')
    
    return result

Complexity

• Time: O(n log n) where n is the number of activity records
• Space: O(n) for storing daily user sets
• Notes: This approach is useful when you need to calculate relative date ranges dynamically.