Customers With Longest Consecutive Transaction Streak

Hard

A payments intelligence team wants to identify customers with the strongest day-by-day transaction consistency. Table: Transactions transaction_id: unique transaction identifier customer_id: customer identifier transaction_date: transaction date in YYYY-MM-DD format amount: transaction value Data guarantee: For any customer, there is at most one transaction row per calendar date. Streak definition: A consecutive streak is a run of transaction dates where every next date is exactly one day after the previous date. Each customer has a longest streak length (their maximum run length). Task: Return all customer IDs whose longest streak length is the highest among all customers. Output requirements: Return exactly one column named customer_id. Sort output by customer_id in ascending order. Important note: The amount column is metadata in this task and does not affect streak length. 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    |

Output

[
  {"customer_id":101},
  {"customer_id":105}
]

Explanation

Customers 101 and 105 each have a longest streak of 3 consecutive days. Customer 102 has a best streak of 2 days, so it is not selected.

Example 2

Input

Transactions:
| transaction_id | customer_id | transaction_date | amount |
|----------------|-------------|------------------|--------|
| 10             | 201         | 2024-01-30       | 800    |
| 11             | 201         | 2024-01-31       | 900    |
| 12             | 201         | 2024-02-01       | 950    |
| 13             | 201         | 2024-02-02       | 1000   |
| 14             | 202         | 2024-01-10       | 10000  |
| 15             | 202         | 2024-01-12       | 5      |
| 16             | 202         | 2024-01-13       | 7      |
| 17             | 203         | 2023-12-31       | 40     |
| 18             | 203         | 2024-01-01       | 60     |
| 19             | 203         | 2024-01-02       | 80     |
| 20             | 203         | 2024-01-03       | 20     |

Output

[
  {"customer_id":201},
  {"customer_id":203}
]

Explanation

Customers 201 and 203 both achieve streak length 4. Amount sizes do not influence the streak metric.

Example 3

Input

Transactions:
| transaction_id | customer_id | transaction_date | amount |
|----------------|-------------|------------------|--------|
| 30             | 300         | 2025-06-05       | 10     |
| 31             | 301         | 2025-06-20       | 20     |
| 32             | 302         | 2025-07-01       | 30     |

Output

[
  {"customer_id":300},
  {"customer_id":301},
  {"customer_id":302}
]

Explanation

Every customer has longest streak length 1, so all customers tie for the global maximum.

Constraints

0 <= number of rows in Transactions <= 200000
transaction_id values are unique
1 <= transaction_id <= 1000000000
1 <= customer_id <= 1000000000
1 <= amount <= 100000
transaction_date is a valid date in YYYY-MM-DD format
(customer_id, transaction_date) is unique
Consecutive means adjacent transaction dates differ by exactly one day
Longest streak for a customer is their maximum consecutive-day run length
Return all customers whose longest streak equals the global maximum
Output must be sorted by customer_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column named customer_id

Loading problem...

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

Customers With Longest Consecutive Transaction Streak

Hard

A payments intelligence team wants to identify customers with the strongest day-by-day transaction consistency. Table: Transactions transaction_id: unique transaction identifier customer_id: customer identifier transaction_date: transaction date in YYYY-MM-DD format amount: transaction value Data guarantee: For any customer, there is at most one transaction row per calendar date. Streak definition: A consecutive streak is a run of transaction dates where every next date is exactly one day after the previous date. Each customer has a longest streak length (their maximum run length). Task: Return all customer IDs whose longest streak length is the highest among all customers. Output requirements: Return exactly one column named customer_id. Sort output by customer_id in ascending order. Important note: The amount column is metadata in this task and does not affect streak length. 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    |

Output

[
  {"customer_id":101},
  {"customer_id":105}
]

Explanation

Customers 101 and 105 each have a longest streak of 3 consecutive days. Customer 102 has a best streak of 2 days, so it is not selected.

Example 2

Input

Transactions:
| transaction_id | customer_id | transaction_date | amount |
|----------------|-------------|------------------|--------|
| 10             | 201         | 2024-01-30       | 800    |
| 11             | 201         | 2024-01-31       | 900    |
| 12             | 201         | 2024-02-01       | 950    |
| 13             | 201         | 2024-02-02       | 1000   |
| 14             | 202         | 2024-01-10       | 10000  |
| 15             | 202         | 2024-01-12       | 5      |
| 16             | 202         | 2024-01-13       | 7      |
| 17             | 203         | 2023-12-31       | 40     |
| 18             | 203         | 2024-01-01       | 60     |
| 19             | 203         | 2024-01-02       | 80     |
| 20             | 203         | 2024-01-03       | 20     |

Output

[
  {"customer_id":201},
  {"customer_id":203}
]

Explanation

Customers 201 and 203 both achieve streak length 4. Amount sizes do not influence the streak metric.

Example 3

Input

Transactions:
| transaction_id | customer_id | transaction_date | amount |
|----------------|-------------|------------------|--------|
| 30             | 300         | 2025-06-05       | 10     |
| 31             | 301         | 2025-06-20       | 20     |
| 32             | 302         | 2025-07-01       | 30     |

Output

[
  {"customer_id":300},
  {"customer_id":301},
  {"customer_id":302}
]

Explanation

Every customer has longest streak length 1, so all customers tie for the global maximum.

Constraints

0 <= number of rows in Transactions <= 200000
transaction_id values are unique
1 <= transaction_id <= 1000000000
1 <= customer_id <= 1000000000
1 <= amount <= 100000
transaction_date is a valid date in YYYY-MM-DD format
(customer_id, transaction_date) is unique
Consecutive means adjacent transaction dates differ by exactly one day
Longest streak for a customer is their maximum consecutive-day run length
Return all customers whose longest streak equals the global maximum
Output must be sorted by customer_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column named customer_id