00:00:00

Description

Editorial

Customer Transaction Momentum Windows

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Transactions =

[{"transaction_id":2,"customer_id":101,"transaction_date":"2023-05-02","amount":150},{"transaction_id":12,"customer_id":105,"transaction_date":"2023-05-13","amount":260},{"transaction_id":10,"customer_id":105,"transaction_date":"2023-05-04","amount":300},{"transaction_id":9,"customer_id":105,"transaction_date":"2023-05-03","amount":200},{"transaction_id":3,"customer_id":101,"transaction_date":"2023-05-03","amount":200},{"transaction_id":6,"customer_id":102,"transaction_date":"2023-05-04","amount":200},{"transaction_id":1,"customer_id":101,"transaction_date":"2023-05-01","amount":100},{"transaction_id":5,"customer_id":102,"transaction_date":"2023-05-03","amount":100},{"transaction_id":7,"customer_id":105,"transaction_date":"2023-05-01","amount":100},{"transaction_id":13,"customer_id":105,"transaction_date":"2023-05-14","amount":270},{"transaction_id":4,"customer_id":102,"transaction_date":"2023-05-01","amount":50},{"transaction_id":11,"customer_id":105,"transaction_date":"2023-05-12","amount":250},{"transaction_id":8,"customer_id":105,"transaction_date":"2023-05-02","amount":150}]

Loading problem...

101

00:00:00

Description

Editorial

Customer Transaction Momentum Windows

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Transactions =

Customer Transaction Momentum Windows

Hints

Customer Transaction Momentum Windows

Hints