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

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 |

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 |

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 |