Find Bursty Behavior - Problem

Given a table Posts, find users who demonstrate bursty behavior in their posting patterns during February 2024.

Bursty behavior is defined as any period of 7 consecutive days where a user's posting frequency is at least twice their average weekly posting frequency for February 2024.

  • Only include dates from February 1 to February 28 in your analysis
  • February is counted as having exactly 4 weeks
  • Return results ordered by user_id in ascending order

Table Schema

Posts
Column Name Type Description
post_id PK int Primary key, unique identifier for each post
user_id int Identifier for the user who made the post
post_date date Date when the post was made
Primary Key: post_id
Note: Each row represents a post made by a user on a specific date

Input & Output

Example 1 — Basic Bursty Behavior Detection
Input Table:
post_id user_id post_date
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:
user_id max_7day_posts avg_weekly_posts
1 1 0.2500
2 1 0.2500
5 1 0.2500
💡 Note:

Users 1, 2, and 5 each made 1 post in February, resulting in 0.25 average weekly posts. Their maximum 7-day count of 1 is at least twice their average (1 ≥ 2 × 0.25 = 0.5). User 3 made 3 posts (0.75 weekly average) but their max 7-day count of 3 is not twice the average (3 < 2 × 0.75 = 1.5).
Example 2 — No Bursty Users
Input Table:
post_id user_id post_date
1 1 2024-02-01
2 1 2024-02-08
3 1 2024-02-15
4 1 2024-02-22
Output:
user_id max_7day_posts avg_weekly_posts
💡 Note:

User 1 made 4 evenly distributed posts (1 per week), resulting in 1.0 average weekly posts. Their maximum 7-day count is 1, which is not at least twice their average (1 < 2 × 1.0 = 2.0), so no bursty behavior is detected.

Constraints

  • 1 ≤ post_id ≤ 1000
  • 1 ≤ user_id ≤ 100
  • post_date is a valid date
  • Only February 2024 dates are considered for analysis

Visualization

Tap to expand
Find Bursty Behavior - Problem OverviewPosts Tableuser_idpost_date102-27302-25302-14Rolling 7-Day AnalysisFor each user:• Max posts in any 7 days• Average weekly postsBursty Usersuser_idis_bursty1✓2✓5✓Condition: max_7day_posts ≥ 2 × avg_weekly_postsUser 1: 1 post → 0.25 avg → 1 ≥ 0.5 ✓ (Bursty)User 3: 3 posts → 0.75 avg → 3 < 1.5 ✗ (Not Bursty)February = 4 weeks for average calculation
Understanding the Visualization
1
Input
Posts table with user_id and post_date
2
Analysis
Rolling 7-day windows and averages
3
Output
Users with bursty posting patterns
Key Takeaway
🎯 Key Insight: Use rolling windows to detect when posting frequency spikes above normal patterns

Related Problems

Asked in
Meta 23 Twitter 18
23.4K Views
Medium Frequency
~20 min Avg. Time
892 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