Find Bursty Behavior - Practice Coding Problems

Find Bursty Behavior - Problem

Database Medium

You are a social media data analyst tasked with identifying users who exhibit bursty posting behavior during February 2024. This is a common pattern in social networks where users have periods of high activity followed by quieter times.

Bursty behavior is defined as any 7-day period where a user's posting frequency is at least twice their average weekly posting frequency for the entire month.

Given a Posts table with columns:

post_id (int) - Primary key
user_id (int) - User identifier
post_date (date) - When the post was made

Your task: Find all users who demonstrate bursty behavior and return their user_id, max_7day_posts (maximum posts in any 7-day period), and avg_weekly_posts (average posts per week in February).

Important: Only analyze posts from February 1-28, 2024 (exactly 4 weeks). Order results by user_id ascending.

Input & Output

example_1.sql — Basic Bursty Users

$ Input: Posts = [[1,1,'2024-02-27'],[2,5,'2024-02-06'],[3,3,'2024-02-25'],[4,3,'2024-02-14'],[5,3,'2024-02-06'],[6,2,'2024-02-25']]

› Output: [[1,1,0.2500],[2,1,0.2500],[5,1,0.2500]]

💡 Note: Users 1, 2, and 5 each made only 1 post in February (avg = 0.25 posts/week). Their maximum 7-day window also contains 1 post, which is ≥ 2×0.25 = 0.5, so they show bursty behavior. User 3 made 3 posts but max 7-day window has only 2 posts, which is < 2×0.75 = 1.5.

example_2.sql — High Activity User

$ Input: Posts = [[1,1,'2024-02-01'],[2,1,'2024-02-02'],[3,1,'2024-02-03'],[4,1,'2024-02-04'],[5,1,'2024-02-15']]

› Output: [[1,4,1.2500]]

💡 Note: User 1 made 5 posts total (avg = 1.25 posts/week). Maximum 7-day window contains 4 posts (Feb 1-4), and 4 ≥ 2×1.25 = 2.5, so this user shows bursty behavior.

example_3.sql — No Bursty Users

$ Input: Posts = [[1,1,'2024-02-01'],[2,1,'2024-02-08'],[3,1,'2024-02-15'],[4,1,'2024-02-22']]

› Output: []

💡 Note: User 1 made 4 posts evenly distributed (avg = 1.0 posts/week). Maximum 7-day window contains only 1 post, which is < 2×1.0 = 2.0, so no bursty behavior detected.

Visualization

Tap to expand

Understanding the Visualization

Timeline View

Visualize each user's posts as dots on a February timeline

Sliding Window

Move a 7-day window across the timeline, counting posts in each position

Peak Detection

Identify the window with the maximum number of posts

Burst Analysis

Compare peak activity to monthly average - if 2x higher, it's bursty!

Key Takeaway

🎯 Key Insight: SQL window functions with date ranges provide the most efficient solution for calculating rolling time-based aggregates and detecting activity patterns.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Where n is number of posts, dominated by sorting for window function

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate results and window function calculations

⚡ Linearithmic Space

Constraints

1 ≤ post_id ≤ 10⁶
1 ≤ user_id ≤ 10⁵
post_date is in YYYY-MM-DD format
Only posts from February 1-28, 2024 are considered
Each post_id is unique
Result must be ordered by user_id ascending

Asked in

f Meta 45 t Twitter 38 in LinkedIn 32 📷 Instagram 28 🎵 TikTok 24

The optimal solution uses SQL window functions with RANGE BETWEEN to efficiently calculate rolling 7-day post counts. Key insight: leverage database optimizations for date arithmetic and window calculations rather than manual nested loops.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Nested Date Loops)	O(n × d)	O(u)	Check every possible 7-day window for each user manually
Optimized SQL with Window Functions	O(n log n)	O(n)	Use SQL window functions to efficiently calculate rolling sums and aggregates

Brute Force (Nested Date Loops) — Algorithm Steps

Get all users who posted in February 2024
For each user, calculate their total posts and average weekly posts
For each user, iterate through all possible 7-day windows (Feb 1-7, Feb 2-8, etc.)
Count posts in each 7-day window
Check if any window has at least 2x the average
Return users meeting the criteria

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter Posts

Extract all posts from February 2024

Group by User

Organize posts by user_id

Check Windows

For each user, check all 22 possible 7-day windows

Compare

Find maximum window count and compare to 2x average

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int post_id;
    int user_id;
    int year, month, day;
} Post;

typedef struct {
    int user_id;
    int max_7day_posts;
    double avg_weekly_posts;
} Result;

int compare_results(const void *a, const void *b) {
    return ((Result*)a)->user_id - ((Result*)b)->user_id;
}

int findBurstyBehavior(Post posts[], int n, Result results[]) {
    int user_posts[1000][100];  // user_id -> array of days
    int user_counts[1000] = {0};
    
    // Collect February 2024 posts
    for (int i = 0; i < n; i++) {
        if (posts[i].year == 2024 && posts[i].month == 2) {
            int uid = posts[i].user_id;
            user_posts[uid][user_counts[uid]++] = posts[i].day;
        }
    }
    
    int result_count = 0;
    
    for (int uid = 1; uid < 1000; uid++) {
        if (user_counts[uid] == 0) continue;
        
        double avg_weekly_posts = user_counts[uid] / 4.0;
        int max_7day_posts = 0;
        
        // Check all 7-day windows
        for (int start_day = 1; start_day <= 22; start_day++) {
            int end_day = start_day + 6;
            int posts_in_window = 0;
            
            for (int j = 0; j < user_counts[uid]; j++) {
                if (user_posts[uid][j] >= start_day && user_posts[uid][j] <= end_day) {
                    posts_in_window++;
                }
            }
            
            if (posts_in_window > max_7day_posts) {
                max_7day_posts = posts_in_window;
            }
        }
        
        if (max_7day_posts >= 2 * avg_weekly_posts) {
            results[result_count].user_id = uid;
            results[result_count].max_7day_posts = max_7day_posts;
            results[result_count].avg_weekly_posts = avg_weekly_posts;
            result_count++;
        }
    }
    
    qsort(results, result_count, sizeof(Result), compare_results);
    return result_count;
}

Time & Space Complexity

Time Complexity

⏱️

O(n × d)

Where n is number of posts and d is number of days (28), checking each date range

✓ Linear Growth

Space Complexity

O(u)

Where u is number of unique users to store results

✓ Linear Space

Constraints

1 ≤ post_id ≤ 10⁶
1 ≤ user_id ≤ 10⁵
post_date is in YYYY-MM-DD format
Only posts from February 1-28, 2024 are considered
Each post_id is unique
Result must be ordered by user_id ascending

43.6K Views

Medium-High Frequency

~25 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 (Nested Date Loops) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler