00:00:00

Description

Editorial

February Posting Burst Detector

MEDIUM20 pts

A social publishing analytics team wants to identify users with concentrated activity spikes during a fixed monthly cycle.

You are given one table:

Posts

post_id: unique identifier for each post
user_id: author identifier
post_date: calendar date when the post was published

Analysis scope:

Include only posts with post_date in [2024-02-01, 2024-02-28].
Treat February as exactly 4 weeks.

For each user in this scope, compute:

avg_weekly_posts = total number of scoped posts / 4
max_7day_posts = maximum number of posts observed in any 7 consecutive-day window within the same scope

A user is classified as bursty if:

max_7day_posts >= 2 * avg_weekly_posts

Output requirements:

Return exactly these columns in order: user_id, max_7day_posts, avg_weekly_posts
Sort by user_id in ascending order
avg_weekly_posts should be reported to 4 decimal places

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

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 |

Output

[
  {"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.

Example

Input

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 |

Output

[
  {"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.

Example

Input

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 |

Output

[
  {"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.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Posts <= 200,000
1 <= post_id <= 2,000,000,000
1 <= user_id <= 2,000,000,000
post_id is unique within each dataset
post_date is a valid date value
Only dates from 2024-02-01 to 2024-02-28 are included in the computation
7-day windows are consecutive calendar windows inside this fixed 28-day period
A user is output only if max_7day_posts >= 2 * avg_weekly_posts
Output columns must be exactly: user_id, max_7day_posts, avg_weekly_posts
Sort output by user_id ASC
SQL submissions are evaluated with SQLite 3.27.2
Pandas submissions must return a DataFrame with the required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Posts =

[{"post_id":1,"user_id":10,"post_date":"2024-02-01"},{"post_id":2,"user_id":10,"post_date":"2024-02-02"},{"post_id":3,"user_id":10,"post_date":"2024-02-03"},{"post_id":4,"user_id":10,"post_date":"2024-02-17"},{"post_id":5,"user_id":20,"post_date":"2024-02-01"},{"post_id":6,"user_id":20,"post_date":"2024-02-10"},{"post_id":7,"user_id":20,"post_date":"2024-02-19"},{"post_id":8,"user_id":20,"post_date":"2024-02-28"}]

Loading problem...

101

00:00:00

Description

Editorial

February Posting Burst Detector

MEDIUM20 pts

A social publishing analytics team wants to identify users with concentrated activity spikes during a fixed monthly cycle.

You are given one table:

Posts

post_id: unique identifier for each post
user_id: author identifier
post_date: calendar date when the post was published

Analysis scope:

Include only posts with post_date in [2024-02-01, 2024-02-28].
Treat February as exactly 4 weeks.

For each user in this scope, compute:

avg_weekly_posts = total number of scoped posts / 4
max_7day_posts = maximum number of posts observed in any 7 consecutive-day window within the same scope

A user is classified as bursty if:

max_7day_posts >= 2 * avg_weekly_posts

Output requirements:

Return exactly these columns in order: user_id, max_7day_posts, avg_weekly_posts
Sort by user_id in ascending order
avg_weekly_posts should be reported to 4 decimal places

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

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 |

Output

[
  {"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.

Example

Input

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 |

Output

[
  {"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.

Example

Input

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 |

Output

[
  {"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.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Posts <= 200,000
1 <= post_id <= 2,000,000,000
1 <= user_id <= 2,000,000,000
post_id is unique within each dataset
post_date is a valid date value
Only dates from 2024-02-01 to 2024-02-28 are included in the computation
7-day windows are consecutive calendar windows inside this fixed 28-day period
A user is output only if max_7day_posts >= 2 * avg_weekly_posts
Output columns must be exactly: user_id, max_7day_posts, avg_weekly_posts
Sort output by user_id ASC
SQL submissions are evaluated with SQLite 3.27.2
Pandas submissions must return a DataFrame with the required columns

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Posts =

February Posting Burst Detector

Hints

February Posting Burst Detector

Hints