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_idin ascending order
Table Schema
| 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 |
Input & Output
| 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 |
| user_id | max_7day_posts | avg_weekly_posts |
|---|---|---|
| 1 | 1 | 0.2500 |
| 2 | 1 | 0.2500 |
| 5 | 1 | 0.2500 |
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).
| 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 |
| user_id | max_7day_posts | avg_weekly_posts |
|---|
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_dateis a valid date - Only February 2024 dates are considered for analysis