Consecutive transactions with increasing amounts

# Table: Transactions
# 
# +------------------+------+
# | Column Name      | Type |
# +------------------+------+
# | transaction_id   | int  |
# | customer_id      | int  |
# | transaction_date | date |
# | amount           | int  |
# +------------------+------+
# transaction_id is the primary key of this table.
# Each row contains information about transactions that includes unique (customer_id, transaction_date) along with the corresponding customer_id and amount.
# 
# Write an SQL query to find the customers who have made consecutive transactions with increasing amount for at least three consecutive days. Include the customer_id, start date of the consecutive transactions period and the end date of the consecutive transactions period. There can be multiple consecutive transactions by a customer.
# 
# Return the result table ordered by customer_id, consecutive_start, consecutive_end in ascending order.
# 
# The query result format is in the following example.
#
# Example 1:
# Input: 
# Transactions table:
# +----------------+-------------+------------------+--------+
# | transaction_id | customer_id | transaction_date | amount |
# +----------------+-------------+------------------+--------+
# | 1              | 101         | 2023-05-01       | 100    |
# | 2              | 101         | 2023-05-02       | 150    |
# | 3              | 101         | 2023-05-03       | 200    |
# | 4              | 102         | 2023-05-01       | 50     |
# | 5              | 102         | 2023-05-03       | 100    |
# | 6              | 102         | 2023-05-04       | 200    |
# | 7              | 105         | 2023-05-01       | 100    |
# | 8              | 105         | 2023-05-02       | 150    |
# | 9              | 105         | 2023-05-03       | 200    |
# | 10             | 105         | 2023-05-04       | 300    |
# | 11             | 105         | 2023-05-12       | 250    |
# | 12             | 105         | 2023-05-13       | 260    |
# | 13             | 105         | 2023-05-14       | 270    |
# +----------------+-------------+------------------+--------+
# Output: 
# +-------------+-------------------+-----------------+
# | customer_id | consecutive_start | consecutive_end | 
# +-------------+-------------------+-----------------+
# | 101         |  2023-05-01       | 2023-05-03      | 
# | 105         |  2023-05-01       | 2023-05-04      |
# | 105         |  2023-05-12       | 2023-05-14      | 
# +-------------+-------------------+-----------------+
# Explanation: 
# - customer_id 101 has made consecutive transactions with increasing amounts from May 1st, 2023, to May 3rd, 2023
# - customer_id 102 does not have any consecutive transactions for at least 3 days. 
# - customer_id 105 has two sets of consecutive transactions: from May 1st, 2023, to May 4th, 2023, and from May 12th, 2023, to May 14th, 2023. 
# customer_id is sorted in ascending order.

import pandas as pd

def consecutive_increasing_transactions(transactions: pd.DataFrame) -> pd.DataFrame:
  transactions['transaction_date'] = pd.to_datetime(transactions['transaction_date'])
  transactions = transactions.sort_values(['customer_id', 'transaction_date'])
  
  # For each customer, check consecutive day pairs with increasing amount
  transactions['prev_date'] = transactions.groupby('customer_id')['transaction_date'].shift(1)
  transactions['prev_amount'] = transactions.groupby('customer_id')['amount'].shift(1)
  
  # Mark where a new group starts (not consecutive day or not increasing amount)
  transactions['is_consecutive'] = (
    (transactions['transaction_date'] - transactions['prev_date']).dt.days.eq(1) &
    (transactions['amount'] > transactions['prev_amount'])
  )
  
  # Assign group IDs using cumulative sum of non-consecutive markers
  transactions['group'] = (~transactions['is_consecutive']).cumsum()
  
  # Group by customer_id and group, get start/end dates and count
  result = transactions.groupby(['customer_id', 'group']).agg(
    consecutive_start=('transaction_date', 'min'),
    consecutive_end=('transaction_date', 'max'),
    count=('transaction_date', 'size')
  ).reset_index()
  
  # Filter groups with at least 3 consecutive transactions
  result = result[result['count'] >= 3][['customer_id', 'consecutive_start', 'consecutive_end']]
  return result.sort_values(['customer_id', 'consecutive_start', 'consecutive_end']).reset_index(drop=True)