User Activity for the Past 30 Days I | LeetCode 1141

This comprehensive study note covers LeetCode problem 1141: User Activity for the Past 30 Days I, featured as Question 24 in the "Crack SQL Interview in 50 Questions" series. This guide breaks down the problem statement, mental models, naive vs. optimized approaches, execution workflows, and production-grade SQL writing practices.

1. Why Do We Need This? (Problem Statement & Status Quo)

In modern web analytics, product management, and business intelligence (BI), tracking Daily Active Users (DAU) is a foundational metric. It measures user engagement and platform stickiness over time.

• The Objective: Write a SQL query to find the daily active user count for a period of 30 days ending with 2019-07-27 (inclusive).
• Definition of Active User: A user is considered active on a specific day if they executed at least one activity on that calendar day.
• Output Requirements: Return the result table containing the day and the corresponding active_users count, ordered in any sequence.

2. Table Schema & Sample Data

The input database contains a single table named Activity with four columns:

Note: The table may contain duplicate rows (i.e., a single user performing multiple distinct actions within the same or different sessions on the same calendar day). Each session belongs to exactly one user.

Sample Input Table (Activity)

3. Core Mental Model & Date Range Derivation

Real-Life Analogy

Imagine running a physical brick-and-mortar storefront or community center. You want to audit daily attendance logs for the last month. You are given a strict closing date (e.g., July 27th) and told to look backward exactly 30 calendar days inclusive. Any visitor who walked through the doors on any of those days counts toward that day's headcount. However, if multiple visitors sign the logbook multiple times on the same day, you only count each unique person once.

Mathematical Date Boundaries

• End Date: 2019-07-27 (Given)
• Period Length: 30 days inclusive (meaning July 27th is Day 1 of the backward window, or Day 30 counting forward).
• Start Date Calculation: Counting backward 30 days from July 27th, 2019 lands precisely on 2019-06-28.

Therefore, our target date filter window is:

4. Step-by-Step Data Filtering & Transformation Walkthrough

Let us trace how the sample rows are processed against our date filter and aggregation rules:

1. Filter Out-of-Range Rows:

   • Rows with activity_date = '2019-06-25' fall outside our window (< '2019-06-28') and are immediately discarded.
   • Remaining rows fall between June 28, 2019, and July 27, 2019.

2. Group by Calendar Date (activity_date):

   • Date: 2019-07-20
     • Associated user_id values: [1, 1, 2, 3]
     • Extract Distinct user_id values: {1, 2, 3}
     • Count of distinct active users: 3 (Correction note: In the transcript example, rows for user 3 were illustrative; let's adhere strictly to distinct unique aggregation).
   • Date: 2019-07-21
     • Associated user_id values: [2, 3]
     • Extract Distinct user_id values: {2, 3}
     • Count of distinct active users: 2

5. Naive Approach vs. Production-Optimized Query

Naive Approach (Prone to Overcounting)

A beginner might forget to use the DISTINCT keyword inside the aggregation function, resulting in duplicate user counts when a single user performs multiple actions on the same day:

-- Naive Query (Incorrect because it double-counts users)
SELECT 
    activity_date AS day,
    COUNT(user_id) AS active_users
FROM Activity
WHERE activity_date BETWEEN '2019-06-28' AND '2019-07-27'
GROUP BY activity_date;

Production-Optimized Solution

To guarantee accuracy, we apply COUNT(DISTINCT user_id) to filter out duplicate user activity entries per calendar day:

SELECT 
    activity_date AS day,
    COUNT(DISTINCT user_id) AS active_users
FROM Activity
WHERE activity_date >= '2019-06-28' 
  AND activity_date <= '2019-07-27'
GROUP BY activity_date;

6. Complete Execution Flow Walkthrough

Below is the vertical execution flow of how the SQL engine processes our optimized query:

Activity Table (Raw Rows)
   |
   | executes WHERE clause (Filters rows where activity_date BETWEEN '2019-06-28' AND '2019-07-27')
   v
Filtered Rows Set
   |
   | executes GROUP BY activity_date (Buckets rows into distinct calendar days)
   v
Grouped Day Buckets
   |
   | executes SELECT & COUNT(DISTINCT user_id) (Computes unique users per bucket) & Aliases columns
   v
Final Output Result Table (day, active_users)

7. Industry Reality & Performance Pitfalls

When executing date-range queries on massive datasets in production environments (such as MySQL, PostgreSQL, or Snowflake), keep the following performance considerations in mind:

• Index on Date Columns: Without an index on activity_date, the database performs a full table scan ($O(N)$ complexity) across millions of historical user action logs. Creating an index on activity_date transforms this into an efficient range scan.
• Composite Indexing: For analytical queries filtering by date and aggregating by user, a composite index (activity_date, user_id) can significantly speed up the distinct counting operation.
• Date Literal Formats: Always use standard ISO 8601 string literals (YYYY-MM-DD) when comparing date fields in SQL to prevent implicit type conversion errors across different database dialects.

8. Summary and Key Takeaways

• Clear Problem Scope: The task requires calculating Daily Active Users (DAU) over a 30-day window ending on 2019-07-27 inclusive.
• Date Boundary Derivation: Counting backward 30 days inclusively from July 27th establishes our lower bound at June 28th (2019-06-28).
• Filtering with WHERE: Use comparison operators (>= and <=) or the BETWEEN operator to isolate the target date range and eliminate out-of-bounds historical data.
• Handling Duplicates: Always use COUNT(DISTINCT user_id) rather than COUNT(user_id) to ensure each user is counted only once per calendar day regardless of activity volume.
• Grouping Results: Use GROUP BY activity_date to aggregate metrics cleanly on a per-day basis.
• Result Aliasing: Use the AS keyword to rename columns to day and active_users as specified in the output requirements.
• Performance Optimization: Ensure proper indexing on date columns in production databases to prevent costly full table scans.