Customer Transaction Momentum Windows

Hard

A risk analytics team wants to detect stable growth behavior in daily transaction streams. Table: Transactions transaction_id: unique event identifier customer_id: customer identifier transaction_date: transaction day in YYYY-MM-DD format amount: transaction amount for that day Business definition of a growth window: A growth window is a maximal sequence of rows for one customer such that: Dates are consecutive calendar days. Each next amount is strictly larger than the previous day's amount. The sequence length is at least 3 days. Important notes: (customer_id, transaction_date) is unique. A gap in dates breaks the window. An equal or lower amount also breaks the window. A customer may have multiple qualifying windows. Task: Return every qualifying window with columns: customer_id consecutive_start consecutive_end Sort by customer_id, consecutive_start, consecutive_end in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Transactions:
| 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":101,"consecutive_start":"2023-05-01","consecutive_end":"2023-05-03"},
  {"customer_id":105,"consecutive_start":"2023-05-01","consecutive_end":"2023-05-04"},
  {"customer_id":105,"consecutive_start":"2023-05-12","consecutive_end":"2023-05-14"}
]

Explanation

Customer 101 has one 3-day increasing run. Customer 105 has two qualifying runs. Customer 102 has no 3-day consecutive increasing period because of a date gap.

Example 2

Input

Transactions:
| transaction_id | customer_id | transaction_date | amount |
|----------------|-------------|------------------|--------|
| 20             | 201         | 2024-01-10       | 300    |
| 21             | 201         | 2024-01-11       | 320    |
| 22             | 201         | 2024-01-12       | 320    |
| 23             | 201         | 2024-01-13       | 400    |
| 24             | 202         | 2024-04-01       | 10     |
| 25             | 202         | 2024-04-02       | 20     |
| 26             | 202         | 2024-04-03       | 30     |
| 27             | 202         | 2024-04-06       | 90     |
| 28             | 202         | 2024-04-07       | 95     |

Output

[
  {"customer_id":202,"consecutive_start":"2024-04-01","consecutive_end":"2024-04-03"}
]

Explanation

Customer 201 breaks on 2024-01-12 because amount is not strictly greater than the previous day. Customer 202 has a qualifying 3-day run and a separate non-qualifying 2-day run.

Example 3

Input

Transactions:
| transaction_id | customer_id | transaction_date | amount |
|----------------|-------------|------------------|--------|
| 30             | 301         | 2020-02-27       | 50     |
| 31             | 301         | 2020-02-28       | 55     |
| 32             | 301         | 2020-02-29       | 70     |
| 33             | 301         | 2020-03-01       | 100    |
| 34             | 302         | 2021-12-30       | 500    |
| 35             | 302         | 2021-12-31       | 600    |
| 36             | 302         | 2022-01-01       | 550    |

Output

[
  {"customer_id":301,"consecutive_start":"2020-02-27","consecutive_end":"2020-03-01"}
]

Explanation

Leap-day transitions are handled as normal calendar continuity. Customer 302 fails because 2022-01-01 amount drops from 600 to 550.

Constraints

0 <= number of rows in Transactions <= 200000
transaction_id is unique
(customer_id, transaction_date) is unique
1 <= transaction_id <= 1000000000
1 <= customer_id <= 1000000000
1 <= amount <= 1000000
transaction_date is a valid date in YYYY-MM-DD format
A valid window must have at least 3 rows
Inside one valid window, date gap between adjacent rows is exactly 1 day
Inside one valid window, amount must be strictly increasing between adjacent rows
Return exactly columns: customer_id, consecutive_start, consecutive_end
Sort output by customer_id, consecutive_start, consecutive_end ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the exact required columns

Transactions: | 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 |

[ {"customer_id":101,"consecutive_start":"2023-05-01","consecutive_end":"2023-05-03"}, {"customer_id":105,"consecutive_start":"2023-05-01","consecutive_end":"2023-05-04"}, {"customer_id":105,"consecutive_start":"2023-05-12","consecutive_end":"2023-05-14"} ]

Transactions: | transaction_id | customer_id | transaction_date | amount | |----------------|-------------|------------------|--------| | 20 | 201 | 2024-01-10 | 300 | | 21 | 201 | 2024-01-11 | 320 | | 22 | 201 | 2024-01-12 | 320 | | 23 | 201 | 2024-01-13 | 400 | | 24 | 202 | 2024-04-01 | 10 | | 25 | 202 | 2024-04-02 | 20 | | 26 | 202 | 2024-04-03 | 30 | | 27 | 202 | 2024-04-06 | 90 | | 28 | 202 | 2024-04-07 | 95 |

Transactions: | transaction_id | customer_id | transaction_date | amount | |----------------|-------------|------------------|--------| | 30 | 301 | 2020-02-27 | 50 | | 31 | 301 | 2020-02-28 | 55 | | 32 | 301 | 2020-02-29 | 70 | | 33 | 301 | 2020-03-01 | 100 | | 34 | 302 | 2021-12-30 | 500 | | 35 | 302 | 2021-12-31 | 600 | | 36 | 302 | 2022-01-01 | 550 |