User Activity for the Past 30 Days II solution and explanation
The problem
Table: Activity
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| user_id | int |
| session_id | int |
| activity_date | date |
| activity_type | enum |
+---------------+---------+
This table may have duplicate rows.
The activity_type column is an ENUM (category) of type ('open_session', 'end_session', 'scroll_down', 'send_message').
The table shows the user activities for a social media website.
Note that each session belongs to exactly one user.
Write a solution to find the average number of sessions per user for a period of 30 days ending 2019-07-27 inclusively, rounded to 2 decimal places. The sessions we want to count for a user are those with at least one activity in that time period.
The result format is in the following example.
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 | send_message | | 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 | | 3 | 5 | 2019-07-21 | open_session | | 3 | 5 | 2019-07-21 | scroll_down | | 3 | 5 | 2019-07-21 | end_session | | 4 | 3 | 2019-06-25 | open_session | | 4 | 3 | 2019-06-25 | end_session | +---------+------------+---------------+---------------+ Output: +---------------------------+ | average_sessions_per_user | +---------------------------+ | 1.33 | +---------------------------+ Explanation: User 1 and 2 each had 1 session in the past 30 days while user 3 had 2 sessions so the average is (1 + 1 + 2) / 3 = 1.33.
Approach overview
Problem Overview: The table Activity stores user activity records with user_id, session_id, and activity_date. The task is to compute the average number of sessions per user during the 30‑day window ending on 2019-07-27. Each session is uniquely identified by session_id, so the goal is to count unique sessions and divide by the number of unique active users in that time range.
Approach 1: Aggregate with COUNT(DISTINCT) (O(n) time, O(1) space)
Filter the dataset to the required 30‑day window using a WHERE condition. Then compute two aggregates: the number of distinct sessions and the number of distinct users. The average sessions per user is simply COUNT(DISTINCT session_id) / COUNT(DISTINCT user_id). Since SQL engines scan the filtered rows once and maintain aggregate counters, the runtime is O(n) where n is the number of rows in the date range, and the additional memory usage is constant.
The key insight is that the problem does not require grouping by user explicitly. Instead of calculating sessions per user and averaging later, you can compute the global ratio directly using two distinct counts. This reduces query complexity and keeps the execution plan simple. MySQL handles COUNT(DISTINCT ...) efficiently using internal hashing or sorting strategies.
This approach is typical in SQL interview questions involving ratios between aggregates. You filter rows, compute aggregates, and derive the final metric in the same query.
Approach 2: Group by User then Compute Average (O(n) time, O(u) space)
Another way is to first compute the number of sessions per user in the 30‑day window. Use GROUP BY user_id and count distinct session_id for each user. That produces a per‑user session count. Then compute the overall average using AVG() over those grouped results, usually through a subquery or derived table.
This approach mirrors the conceptual definition of the metric: sessions per user, then the average of those values. The SQL engine creates an intermediate grouped dataset of size u (number of active users), so the extra space is O(u). Runtime remains O(n) because each activity row is still processed once.
While slightly more verbose, this pattern appears frequently in analytics queries and demonstrates good understanding of database aggregation workflows and aggregation pipelines.
Recommended for interviews: The direct COUNT(DISTINCT) ratio is usually preferred. It is concise, efficient, and shows that you recognize the metric can be computed with two aggregates instead of an intermediate grouping step. The grouped subquery approach is still valuable because it mirrors the conceptual logic and works well when more per‑user metrics are required.
Solution
# Write your MySQL query statement below
WITH
T AS (
SELECT
COUNT(DISTINCT session_id) AS sessions
FROM Activity
WHERE activity_date <= '2019-07-27' AND DATEDIFF('2019-07-27', activity_date) < 30
GROUP BY user_id
)
SELECT IFNULL(ROUND(AVG(sessions), 2), 0) AS average_sessions_per_user
FROM T;Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| COUNT(DISTINCT) Ratio | O(n) | O(1) | Best for concise queries when only the global average is needed |
| Group By User + AVG() | O(n) | O(u) | Useful when computing additional per-user metrics before averaging |
Frequently asked questions
Is User Activity for the Past 30 Days II easy or hard?
User Activity for the Past 30 Days II is classified as Easy. The main challenge is recognizing that the required metric can be computed using two distinct counts over the filtered dataset rather than grouping and averaging manually.
User Activity for the Past 30 Days II Python/Java solution
This problem is designed for SQL rather than Python or Java implementations. The typical solution is a MySQL query that filters the date range and calculates COUNT(DISTINCT session_id) divided by COUNT(DISTINCT user_id) to produce the average sessions per user.
How to solve User Activity for the Past 30 Days II in O(n)?
Filter rows where activity_date falls within the last 30 days ending on 2019-07-27. Then compute COUNT(DISTINCT session_id) and COUNT(DISTINCT user_id) and divide them to get the average sessions per user. Since the query performs a single scan with aggregation, the runtime remains O(n).
What is the best approach for User Activity for the Past 30 Days II?
The most efficient approach uses COUNT(DISTINCT session_id) divided by COUNT(DISTINCT user_id) after filtering the last 30 days of activity. This computes the average sessions per user directly in one SQL query. It runs in O(n) time over the filtered rows and uses constant additional space.
Is User Activity for the Past 30 Days II asked at Google/Amazon/Meta?
SQL aggregation and analytics-style questions similar to this problem appear in interviews at companies like Amazon, Meta, and Google. Interviewers often test filtering by date ranges, distinct counts, and computing derived metrics such as averages or ratios from aggregated data.
What data structure is used in User Activity for the Past 30 Days II?
The solution relies on relational database aggregation rather than traditional data structures. Internally, SQL engines may use hash sets or sorting to implement COUNT(DISTINCT) operations while scanning rows from the Activity table.
What is the time complexity of User Activity for the Past 30 Days II?
The SQL query runs in O(n) time where n is the number of activity rows scanned within the 30‑day window. Aggregate functions such as COUNT(DISTINCT) process the dataset once while maintaining internal counters. Space complexity is typically O(1) unless the database engine builds temporary structures for distinct tracking.