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:
-
Activitytable:user_id: Integer representing the user ID.activity_date: Date when the activity occurred.activity_type: String indicating the type of activity.
-
Usertable: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_dateshould 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
Usertable might have users not present in theActivitytable and vice versa.
Intuition
Brute Force Approach:
- Filter the
Activitytable to include only records from the past 30 days. - Group the activities by
activity_date. - For each date, count the number of unique users who performed activities.
- 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
-
Filter Activities in the Past 30 Days:
- Use a
WHEREclause to filter theActivitytable to include only records from the past 30 days.
- Use a
-
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.
- Group the filtered activities by
-
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.
-
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
COALESCEto 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
Usertable is not directly used in this query, but if needed, it can be joined to ensure user existence. - Empty Input Tables: If either the
ActivityorUsertable is empty, the solution will handle it gracefully by returning an empty result set for the activity counts.