Watch 10 video solutions for Find Bursty Behavior, a medium level problem involving Database. This walkthrough by Dave Burji has 596,394 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
| 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 |
