Find bursty behavior

# 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 1:
# 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.

import pandas as pd

def find_bursty_behavior(posts: pd.DataFrame) -> pd.DataFrame:
  posts['post_date'] = pd.to_datetime(posts['post_date'])
  feb = posts[(posts['post_date'] >= '2024-02-01') & (posts['post_date'] <= '2024-02-28')]
  # Average weekly posts = total posts / 4
  user_total = feb.groupby('user_id').size().reset_index(name='total_posts')
  user_total['avg_weekly_posts'] = user_total['total_posts'] / 4
  # For each user, compute max posts in any 7-day window
  from datetime import timedelta
  results = []
  for uid, group in feb.groupby('user_id'):
    dates = sorted(group['post_date'].dt.date.tolist())
    max_7day = 0
    for d in pd.date_range('2024-02-01', '2024-02-22'):
      d_date = d.date()
      end_date = d_date + timedelta(days=6)
      count = sum(1 for x in dates if d_date <= x <= end_date)
      max_7day = max(max_7day, count)
    results.append({'user_id': uid, 'max_7day_posts': max_7day})
  max_df = pd.DataFrame(results)
  merged = max_df.merge(user_total[['user_id', 'avg_weekly_posts']], on='user_id')
  result = merged[merged['max_7day_posts'] >= 2 * merged['avg_weekly_posts']]
  result = result[['user_id', 'max_7day_posts', 'avg_weekly_posts']].sort_values('user_id').reset_index(drop=True)
  return result