Domain 1EasyCORE

User Activity for the Past 30 Days II

**Restated Problem:** Given two tables, `Activity` and `User`, we need to find the average number of activities per user for each day in the past 30 days. The `Activity` table contains records of user activities with columns `user_id`, `activity_date`, and `activity_type`. The `User` table contains user details with columns `user_id` and `name`.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 1

1142. User Activity for the Past 30 Days II

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 35.4%


Problem Breakdown

Restated Problem: Given two tables, Activity and User, we need to find the average number of activities per user for each day in the past 30 days. The Activity table contains records of user activities with columns user_id, activity_date, and activity_type. The User table contains user details with columns user_id and name.

Inputs:

  • Activity table:

    • user_id: Integer representing the user ID.
    • activity_date: Date when the activity occurred.
    • activity_type: String indicating the type of activity.
  • User table:

    • user_id: Integer representing the user ID.
    • name: String representing the user’s name.

Outputs:

  • A result set with two columns:
    • activity_date: Date for which the average is calculated.
    • average_activities: The average number of activities per user on that date.

Implicit Constraints and Edge Cases:

  • The activity_date should be within the past 30 days from the current date.
  • If there are no activities in a particular day, the result for that day should still be included with an average of 0.
  • Handle cases where the User table might have users not present in the Activity table and vice versa.

Intuition

Brute Force Approach:

  1. Filter the Activity table to include only records from the past 30 days.
  2. Group the activities by activity_date.
  3. For each date, count the number of unique users who performed activities.
  4. Calculate the average number of activities per user for each date.

Why Brute Force is Insufficient:

  • The brute force approach involves filtering and grouping operations which can be computationally expensive if the dataset is large.
  • It doesn’t efficiently handle cases where there are no activities on a particular day, requiring additional checks to ensure all dates in the past 30 days are included.

Key Insight: To optimize the solution, we can leverage SQL window functions and date ranges to efficiently calculate the average number of activities per user for each day. This approach ensures that we handle all edge cases, including days with no activities, more elegantly.

Approach

  1. Filter Activities in the Past 30 Days:

    • Use a WHERE clause to filter the Activity table to include only records from the past 30 days.
  2. Group by Date and Count Unique Users:

    • Group the filtered activities by activity_date.
    • For each date, count the number of unique users who performed activities using the COUNT(DISTINCT user_id) function.
  3. Calculate Average Activities per User:

    • Use a window function to calculate the average number of activities per user for each day.
    • Ensure that all dates in the past 30 days are included by generating a date range and performing an outer join with the aggregated activity data.
  4. Handle Edge Cases:

    • If there are no activities on a particular day, ensure that the result includes that day with an average of 0.
    • Use COALESCE to handle cases where the count might be null due to no activities.

Complexity Analysis

Time Complexity:

  • The time complexity is primarily determined by the filtering and grouping operations. Assuming the dataset size is manageable, the complexity can be considered linear with respect to the number of activity records.

Space Complexity:

  • The space complexity is also linear with respect to the number of unique dates in the past 30 days, as we store intermediate results for each date.

Solution

import pandas as pd

def user_activity(activity: pd.DataFrame, user: pd.DataFrame) -> pd.DataFrame:
    # Filter activities from the past 30 days
    current_date = pd.Timestamp.now()
    start_date = current_date - pd.Timedelta(days=29)  # Include today in the range
    filtered_activities = activity[activity['activity_date'] >= start_date]
    
    # Group by date and count unique users
    daily_activity_counts = filtered_activities.groupby('activity_date').agg(
        user_count=('user_id', 'nunique')
    ).reset_index()
    
    # Generate a date range for the past 30 days
    all_dates = pd.date_range(start=start_date, end=current_date)
    all_dates_df = pd.DataFrame(all_dates, columns=['activity_date'])
    
    # Perform an outer join to include all dates
    result = pd.merge(
        all_dates_df,
        daily_activity_counts,
        on='activity_date',
        how='left'
    )
    
    # Calculate the average activities per user for each day
    result['average_activities'] = result['user_count'].fillna(0)
    
    return result[['activity_date', 'average_activities']]

Edge Cases Handled

  • No Activities on a Day: The solution includes all dates in the past 30 days and sets the average to 0 for days with no activities.
  • Users Not Present in Activity Table: The User table is not directly used in this query, but if needed, it can be joined to ensure user existence.
  • Empty Input Tables: If either the Activity or User table is empty, the solution will handle it gracefully by returning an empty result set for the activity counts.