Daily Peak Transaction IDs

Medium

A payment ledger records each transaction event with a timestamp and amount. Table: transactions transaction_id: unique identifier for a transaction day: event timestamp in datetime format amount: transaction amount for that event Task: For every calendar date, identify the transaction(s) with the highest amount on that date. Rules: Partition by calendar date extracted from day. If more than one transaction reaches the same daily maximum amount, include every tied transaction. Output requirements: Return exactly one column: transaction_id Sort results by transaction_id in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

transactions:
| transaction_id | day                 | amount |
|----------------|---------------------|--------|
| 8              | 2021-04-03 15:57:28 | 57     |
| 9              | 2021-04-28 08:47:25 | 21     |
| 1              | 2021-04-29 13:28:30 | 58     |
| 5              | 2021-04-28 16:39:59 | 40     |
| 6              | 2021-04-29 23:39:28 | 58     |

Output

[
  {"transaction_id":1},
  {"transaction_id":5},
  {"transaction_id":6},
  {"transaction_id":8}
]

Explanation

Daily winners are: 2021-04-03 -> {8}, 2021-04-28 -> {5}, 2021-04-29 -> {1, 6}. The final output is globally sorted by transaction_id.

Example 2

Input

transactions:
| transaction_id | day                 | amount |
|----------------|---------------------|--------|
| 101            | 2024-01-11 00:00:01 | -5     |
| 102            | 2024-01-11 23:59:59 | -2     |
| 103            | 2024-01-12 09:30:00 | 10     |
| 104            | 2024-01-12 11:45:00 | 10     |
| 105            | 2024-01-12 19:05:00 | 7      |

Output

[
  {"transaction_id":102},
  {"transaction_id":103},
  {"transaction_id":104}
]

Explanation

For 2024-01-11, -2 is the maximum. For 2024-01-12, two rows tie at amount 10, so both IDs are returned.

Example 3

Input

transactions:
| transaction_id | day                 | amount |
|----------------|---------------------|--------|
| 7001           | 2025-07-01 12:00:00 | 500    |
| 7002           | 2025-07-02 12:00:00 | 450    |
| 7003           | 2025-07-03 12:00:00 | 600    |
| 7004           | 2025-07-03 23:59:58 | 600    |

Output

[
  {"transaction_id":7001},
  {"transaction_id":7002},
  {"transaction_id":7003},
  {"transaction_id":7004}
]

Explanation

Every date contributes at least one winner. On 2025-07-03, both 7003 and 7004 share the daily peak.

Constraints

1 <= number of rows in transactions <= 300000
1 <= transaction_id <= 1000000000, and transaction_id is unique
day is a valid datetime value
-1000000000 <= amount <= 1000000000
Return exactly one column: transaction_id
Sort result by transaction_id ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with required output column

transactions: | transaction_id | day | amount | |----------------|---------------------|--------| | 8 | 2021-04-03 15:57:28 | 57 | | 9 | 2021-04-28 08:47:25 | 21 | | 1 | 2021-04-29 13:28:30 | 58 | | 5 | 2021-04-28 16:39:59 | 40 | | 6 | 2021-04-29 23:39:28 | 58 |

transactions: | transaction_id | day | amount | |----------------|---------------------|--------| | 101 | 2024-01-11 00:00:01 | -5 | | 102 | 2024-01-11 23:59:59 | -2 | | 103 | 2024-01-12 09:30:00 | 10 | | 104 | 2024-01-12 11:45:00 | 10 | | 105 | 2024-01-12 19:05:00 | 7 |

transactions: | transaction_id | day | amount | |----------------|---------------------|--------| | 7001 | 2025-07-01 12:00:00 | 500 | | 7002 | 2025-07-02 12:00:00 | 450 | | 7003 | 2025-07-03 12:00:00 | 600 | | 7004 | 2025-07-03 23:59:58 | 600 |