OneNoughtOne
0/308
Loading content...
A social publishing analytics team wants to identify users with concentrated activity spikes during a fixed monthly cycle.
You are given one table:
Posts
Analysis scope:
For each user in this scope, compute:
A user is classified as bursty if:
Output requirements:
Supported submission environments:
Posts:
| post_id | user_id | post_date |
|--------:|--------:|------------|
| 1 | 10 | 2024-02-01 |
| 2 | 10 | 2024-02-02 |
| 3 | 10 | 2024-02-03 |
| 4 | 10 | 2024-02-17 |
| 5 | 20 | 2024-02-01 |
| 6 | 20 | 2024-02-10 |
| 7 | 20 | 2024-02-19 |
| 8 | 20 | 2024-02-28 |[
{"user_id":10,"max_7day_posts":3,"avg_weekly_posts":1.0}
]Explanation
User 10 has 4 total posts (avg 1.0/week) and a 7-day peak of 3, so they satisfy 3 >= 2. User 20 has 4 total posts but max_7day_posts is only 1, so they are excluded.
Posts:
| post_id | user_id | post_date |
|--------:|--------:|------------|
| 11 | 31 | 2024-01-31 |
| 12 | 31 | 2024-02-05 |
| 13 | 31 | 2024-02-06 |
| 14 | 31 | 2024-02-07 |
| 15 | 31 | 2024-03-01 |
| 16 | 99 | 2024-02-15 |[
{"user_id":31,"max_7day_posts":3,"avg_weekly_posts":0.75},
{"user_id":99,"max_7day_posts":1,"avg_weekly_posts":0.25}
]Explanation
Rows outside February 1-28 are ignored. The remaining February rows determine both window counts and averages.
Posts:
| post_id | user_id | post_date |
|--------:|--------:|------------|
| 21 | 77 | 2024-02-04 |
| 22 | 77 | 2024-02-05 |
| 23 | 77 | 2024-02-06 |
| 24 | 77 | 2024-02-14 |
| 25 | 77 | 2024-02-22 |
| 26 | 77 | 2024-02-23 |[
{"user_id":77,"max_7day_posts":3,"avg_weekly_posts":1.5}
]Explanation
User 77 has 6 scoped posts, so avg_weekly_posts is 1.5. The strongest 7-day window contains 3 posts, meeting the exact threshold 3 = 2 * 1.5.
Constraints