This version of the User Activity for the Past 30 Days interview question asks for the average number of sessions per user during the 30-day window. A session is defined as a collection of activities performed by a user on a specific day. You need to calculate the total number of sessions across all users and divide it by the total number of unique users, returning the result rounded to two decimal places.
Companies like Meta and Zoom use the User Activity II coding problem to see if a candidate can handle nested aggregations or subqueries. It’s slightly more complex than the first version because it requires you to first define what a session is (usually a unique user_id and session_id pair) and then calculate an average over the entire set.
The Database interview pattern for this typically uses a subquery or a Common Table Expression (CTE). First, you filter the activity table for the correct 30-day window. Then, you select the total count of unique sessions and the total count of unique users. Finally, you perform the division. A simple way is: SELECT ROUND(IFNULL(COUNT(DISTINCT session_id) / COUNT(DISTINCT user_id), 0), 2).
Suppose in the last 30 days:
One frequent error is dividing by zero if there are no active users in the window (using IFNULL or COALESCE is a good safeguard). Another mistake is not distinct-counting the sessions; if a user has multiple activities in one session, the session should only be counted once. Candidates also sometimes forget the rounding requirement.
Practice SQL Aggregation over Aggregates. Understanding how to calculate means, medians, and averages from event streams is a fundamental skill for backend and data engineers. Focus on edge cases where the denominator might be zero.
