Skip to main content
Talentd

Find Bursty Behavior - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase7 min read
Practice this problem

Problem Statement

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:

  • User 1: Made only 1 post in February, resulting in an average of 0.25 posts per week and a max of 1 post in any 7-day period.
  • User 2: Also made just 1 post, with the same average and max 7-day posting frequency as User 1.
  • User 5: Like Users 1 and 2, User 5 made only 1 post throughout February, leading to the same average and max 7-day posting metrics.
  • User 3: Although User 3 made more posts than the others (3 posts), they did not reach twice the average weekly posts in their consecutive 7-day window, so they are not listed in the output.

Note: Output table is ordered by user_id in ascending order.

Approach Overview

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.

Solution

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.

Code

MySQLPython

MySQL

Python

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Brute Force Timestamp ComparisonO(n^2)O(1)Conceptual understanding or very small datasets
Self-Join + Group CountO(n^2) join logic (optimized by DB engine)O(n)Standard SQL solution for detecting time-window activity patterns

Video Solution

The unfair way I got good at Leetcode • Dave Burji • 596,394 views views

Watch 9 more video solutions →

Frequently Asked Questions

Is Find Bursty Behavior easy or hard?
Find Bursty Behavior is typically rated Medium difficulty. The challenge is recognizing that the problem requires comparing rows from the same table within a time window and using a self-join with aggregation to count qualifying events.
Find Bursty Behavior Python/Java solution
In SQL platforms like MySQL, the solution uses a self-join and GROUP BY with COUNT. In Python, the same logic can be implemented using pandas by merging the DataFrame with itself on user_id, filtering rows based on timestamp differences, and aggregating counts per user.
How to solve Find Bursty Behavior in O(n)?
Pure O(n) processing is difficult in SQL because the problem requires comparing timestamps across rows. However, indexing and query optimization allow the database engine to scan relevant ranges efficiently. The practical solution remains a self-join with aggregation, which behaves close to linear for well-indexed tables.
What is the best approach for Find Bursty Behavior?
The most effective solution uses a SQL self-join combined with GROUP BY and COUNT. The table is joined with itself on user_id while filtering rows whose timestamps fall within the defined burst window. Aggregation then counts how many posts occur in that window and keeps only those meeting the threshold. This approach leverages database query optimization and handles large datasets efficiently.
Is Find Bursty Behavior asked at Google/Amazon/Meta?
Time-window analysis questions like this appear frequently in SQL interviews at companies such as Amazon, Meta, and Google. They test understanding of joins, aggregation, and temporal data analysis. Variants include detecting rapid user activity, fraud signals, or unusual event bursts.
What data structure is used in Find Bursty Behavior?
The problem relies on relational table operations rather than traditional in-memory data structures. SQL concepts such as self-joins, grouping, and aggregate functions are the core tools used to compare rows and count events within a time window.
What is the time complexity of Find Bursty Behavior?
The logical complexity is O(n^2) because each row may be compared with other rows from the same user during the self-join. In practice, database engines optimize joins with indexes on user_id and timestamp columns, making the query significantly faster for real workloads.

Ready to solve this problem?

Practice Find Bursty Behavior with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary