User Activity for the Past 30 Days I - Practice Coding Problems

User Activity for the Past 30 Days I - Problem

Database Easy

You're building analytics for a social media platform to track daily active users. Given an Activity table that records every user interaction, you need to calculate the number of unique active users for each day in a specific 30-day period.

Table Schema:

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| session_id    | int     |
| activity_date | date    |
| activity_type | enum    |
+---------------+---------+

The activity_type can be: 'open_session', 'end_session', 'scroll_down', or 'send_message'. Any of these activities counts as the user being active on that day.

Goal: Find the daily active user count for the 30-day period ending on 2019-07-27 (inclusive). This means counting unique users who performed at least one activity each day between 2019-06-28 and 2019-07-27.

Output Format: Return a table with day and active_users columns showing the count of unique active users for each day in the specified range.

Input & Output

example_1.sql — Basic Daily Activity

$ 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 | | 3 | 2 | 2019-07-21 | open_session | | 3 | 2 | 2019-07-21 | end_session | | 4 | 3 | 2019-06-25 | open_session | +--------+------------+---------------+---------------+

› Output: +------------+--------------+ | day | active_users | +------------+--------------+ | 2019-07-20 | 2 | | 2019-07-21 | 2 | +------------+--------------+

💡 Note: For 2019-07-20: Users 1 and 2 were active (user 1 had multiple activities but counts as 1). For 2019-07-21: Users 2 and 3 were active. The activity on 2019-06-25 is outside our 30-day window ending 2019-07-27, so it's excluded.

example_2.sql — Edge Case: Same User Multiple Sessions

$ Input: Activity table: +--------+------------+---------------+---------------+ | user_id| session_id | activity_date | activity_type | +--------+------------+---------------+---------------+ | 1 | 1 | 2019-07-27 | open_session | | 1 | 2 | 2019-07-27 | open_session | | 1 | 1 | 2019-07-27 | send_message | | 1 | 2 | 2019-07-27 | scroll_down | | 2 | 3 | 2019-07-27 | end_session | +--------+------------+---------------+---------------+

› Output: +------------+--------------+ | day | active_users | +------------+--------------+ | 2019-07-27 | 2 | +------------+--------------+

💡 Note: Even though user 1 has multiple sessions and activities on 2019-07-27, they count as only 1 unique active user. User 2 also had activity, so total active users = 2.

example_3.sql — Boundary Date Testing

$ Input: Activity table: +--------+------------+---------------+---------------+ | user_id| session_id | activity_date | activity_type | +--------+------------+---------------+---------------+ | 1 | 1 | 2019-06-27 | open_session | | 1 | 1 | 2019-06-28 | open_session | | 2 | 2 | 2019-07-27 | scroll_down | | 3 | 3 | 2019-07-28 | send_message | +--------+------------+---------------+---------------+

› Output: +------------+--------------+ | day | active_users | +------------+--------------+ | 2019-06-28 | 1 | | 2019-07-27 | 1 | +------------+--------------+

💡 Note: 2019-06-27 is excluded (31 days before 2019-07-27). 2019-06-28 is the start of our 30-day window (30 days before). 2019-07-27 is included (end date). 2019-07-28 is excluded (after end date).

Visualization

Tap to expand

Understanding the Visualization

Filter Time Window

Just like reviewing security footage from the past 30 days, we filter activities between 2019-06-28 and 2019-07-27

Group by Day

Organize all visitor interactions by date, like sorting entry logs into daily folders

Count Unique Visitors

For each day's folder, count how many different people visited (not how many times they entered)

Generate Report

Create a daily summary showing date and unique visitor count for analytics dashboard

Key Takeaway

🎯 Key Insight: Use SQL's built-in aggregation functions (GROUP BY, COUNT DISTINCT) rather than manual counting. Database engines are highly optimized for these operations and can handle large datasets efficiently with internal sorting, hashing, and indexing mechanisms.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Database uses efficient sorting and hashing algorithms for GROUP BY and COUNT DISTINCT

⚡ Linearithmic

Space Complexity

O(d + u)

Space for d distinct dates and u unique users in the result set

✓ Linear Space

Constraints

1 ≤ number of activity records ≤ 10⁴
Each session belongs to exactly one user
Date range: 30 days ending 2019-07-27 inclusive
activity_type is one of: 'open_session', 'end_session', 'scroll_down', 'send_message'
All activity types count as valid user activity

Asked in

f Meta 45 G Google 38 a Amazon 32 ⊞ Microsoft 28

The optimal solution uses SQL GROUP BY with COUNT DISTINCT to efficiently aggregate user activity data. By filtering the 30-day window first, then grouping by date and counting unique users, we leverage the database engine's built-in optimization for O(n log n) performance instead of manually scanning data.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Manual Row Processing)	O(n²)	O(n)	Process each row individually and manually track unique users per day
SQL GROUP BY with COUNT DISTINCT (Optimal)	O(n log n)	O(d + u)	Use SQL's optimized GROUP BY and COUNT DISTINCT to aggregate data efficiently

Brute Force (Manual Row Processing) — Algorithm Steps

Filter activities within the 30-day date range
For each day, scan all matching records
Manually count unique user_ids for each day
Return results without using optimized SQL functions

Visualization

Tap to expand

Step-by-Step Walkthrough

Pick First Date

Start with 2019-06-28

Scan All Records

Look through entire activity table for matching date

Count Unique Users

Manually track unique user_ids for this date

Repeat for Next Date

Move to next date and repeat entire process

Code -

solution.c — C

// Brute force simulation in C
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int user_id;
    char activity_date[11];
} Activity;

typedef struct {
    char day[11];
    int active_users;
} Result;

int solution(Activity* activities, int activity_count, Result* results) {
    char dates[][11] = {"2019-06-28", "2019-06-29", /* ... */ "2019-07-27"};
    int date_count = 30;
    
    for (int d = 0; d < date_count; d++) {
        int user_set[1000] = {0}; // Simple set simulation
        int unique_users = 0;
        
        // Scan all activities for this date
        for (int i = 0; i < activity_count; i++) {
            if (strcmp(activities[i].activity_date, dates[d]) == 0) {
                if (!user_set[activities[i].user_id]) {
                    user_set[activities[i].user_id] = 1;
                    unique_users++;
                }
            }
        }
        
        strcpy(results[d].day, dates[d]);
        results[d].active_users = unique_users;
    }
    
    return date_count;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each day, we potentially scan all n records, leading to quadratic time complexity

⚠ Quadratic Growth

Space Complexity

O(n)

Need to store intermediate results for each day and user combination

⚡ Linearithmic Space

Constraints

1 ≤ number of activity records ≤ 10⁴
Each session belongs to exactly one user
Date range: 30 days ending 2019-07-27 inclusive
activity_type is one of: 'open_session', 'end_session', 'scroll_down', 'send_message'
All activity types count as valid user activity

89.3K Views

High Frequency

~8 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Manual Row Processing) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler