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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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