User Activity for the Past 30 Days I

User Activity for the Past 30 Days I

What is this problem about?

The User Activity for the Past 30 Days I interview question asks you to generate a report from an activity log. You need to find the number of "daily active users" for each day in a specific 30-day window ending on a given date (e.g., 2019-07-27). An active user is defined as someone who performed at least one activity on that day.

Why is this asked in interviews?

This User Activity coding problem is a standard task for data analysis roles at companies like Microsoft and Amazon. It tests your ability to aggregate data over a time series and your familiarity with basic SQL functions like COUNT(DISTINCT ...). It’s a practical test of whether you can generate business-critical metrics from raw event data.

Algorithmic pattern used

The primary Database interview pattern involves filtering the data using a WHERE clause to keep only rows within the 30-day range. Then, you use GROUP BY on the activity date. To count users correctly, you must use COUNT(DISTINCT user_id), as a single user might have performed multiple activities on the same day but should only be counted once.

Example explanation

Activity Log:

• July 20: User 1 (Login), User 1 (Post), User 2 (Login)
• July 21: User 1 (Login)
• July 28: User 3 (Login) Report for July 27th window:

1. July 20: 2 distinct users (User 1 and User 2).
2. July 21: 1 distinct user (User 1).
3. July 28: Not in window. Final output table lists dates and their respective counts.

Common mistakes candidates make

The most common mistake is forgetting the DISTINCT keyword, which leads to overcounting if users have multiple log entries. Another error is calculating the 30-day window incorrectly (e.g., being off by one day). Candidates also sometimes struggle with the specific date syntax of the SQL dialect they are using (PostgreSQL vs. MySQL).

Interview preparation tip

Get comfortable with Date Math in SQL. Know how to add or subtract intervals (e.g., interval '30 days'). Also, always double-check if your results should include days with zero activity (though usually, the grouping logic handles this by simply not returning those rows).