Table: Posts
+-------------+---------+ | Column Name | Type | +-------------+---------+ | post_id | int | | user_id | int | | post_date | date | +-------------+---------+ post_id is the primary key (column with unique values) for this table. Each row of this table contains post_id, user_id, and post_date.
Write a solution to 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 to their average weekly posting frequency for February 2024.
Note: Only include the dates from February 1 to February 28 in your analysis, which means you should count February as having exactly 4 weeks.
Return the result table orderd by user_id in ascending order.
The result format is in the following example.
Example:
Input:
Posts 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 | +---------+----------------+------------------+
Explanation:
Note: Output table is ordered by user_id in ascending order.
Problem Overview: The task identifies users who show bursty behavior—multiple posts created within a short time window. You need to analyze timestamps in a posts table and detect when the same user creates several posts close together in time.
Approach 1: Brute Force Timestamp Comparison (O(n^2) time, O(1) extra space)
The straightforward method compares every post with every other post from the same user. For each record, iterate through all other records and check whether their timestamps fall within the defined time window. Count how many qualifying posts exist for that user during that interval. If the count reaches the burst threshold (for example, three posts within one hour), mark the user as bursty. This approach works conceptually but performs poorly on large datasets because it requires pairwise comparisons across many rows.
Approach 2: Self-Join + Group Count (O(n^2) join time, O(n) space)
The practical SQL solution uses a self-join on the posts table. Join the table to itself on user_id, then restrict pairs where the timestamp difference falls within the burst window. Each post becomes an anchor, and the join collects other posts from the same user occurring shortly after it. Use GROUP BY on the anchor post (or user) and apply COUNT() with a HAVING condition to keep only those groups reaching the required number of posts.
This approach shifts the heavy comparison work to the database engine. SQL optimizers handle joins efficiently, especially when indexes exist on user_id and the timestamp column. Aggregation with GROUP BY ensures only users meeting the burst threshold appear in the result.
Conceptually, the query does three things: match posts from the same user, filter by a small time difference between timestamps, and count how many qualifying posts exist in that window. The combination of self-join and aggregation is a common pattern in database interview questions where time-based relationships between rows must be evaluated.
Recommended for interviews: Interviewers expect the self-join with aggregation approach. The brute-force explanation shows you understand the underlying comparison logic, but the SQL self-join demonstrates practical skill with SQL joins and aggregation functions. It scales better and matches how relational databases are designed to process temporal relationships between rows.
We can use self-join to connect the Posts table with itself. The connection condition is p1.user_id = p2.user_id and p2.post_date is between p1.post_date and 6 days after p1.post_date. Then we group the connection results by p1.user_id and p1.post_id to count the number of posts for each user within 7 days of each day. We save this result in table P.
Next, we count the average number of posts per week for each user in February 2024 and save it in table T. Note that we need to find records where post_date is between 2024-02-01 and 2024-02-28, group the records by user_id, then count the number of posts for each user, and finally divide by 4 to get the average number of posts per week. We save this result in table T.
Finally, we connect tables P and T with the condition P.user_id = T.user_id, then group by user_id to count the maximum number of posts within 7 days for each user. We then filter out records that meet the condition max_7day_posts >= avg_weekly_posts * 2 to get the result. Note that we need to sort in ascending order by user_id.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Brute Force Timestamp Comparison | O(n^2) | O(1) | Conceptual understanding or very small datasets |
| Self-Join + Group Count | O(n^2) join logic (optimized by DB engine) | O(n) | Standard SQL solution for detecting time-window activity patterns |
The unfair way I got good at Leetcode • Dave Burji • 596,394 views views
Watch 9 more video solutions →Practice Find Bursty Behavior with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor