Latest status when timestamps tie

Pandas data engineering interview problem. Difficulty: intermediate. Pattern: Deduplication. About 14 minutes. Part of the Pro drill bank.

Pick the latest status per account, using the load sequence to break ties on the same timestamp. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

df holds account status changes with account_id, status, changed_at and load_seq. changed_at is only accurate to the minute, so two changes can share it. load_seq is a number that grows with every row the loader wrote. Return 1 row per account: the row with the latest changed_at. If several rows share that timestamp, the one with the highest load_seq wins. Keep all 4 columns, sort by account_id, and reset the index. Assign the DataFrame to result.

Requirements

• Return the 4 columns of df.
• Sort by account_id and reset the index.

Constraints

• changed_at strings sort in time order.
• load_seq is unique across the table.

Examples

Input: df account_id | status | changed_at | load_seq 1 | trial | 2024-05-01 10:00 | 11 1 | active | 2024-05-01 10:00 | 12 2 | active | 2024-05-02 09:00 | 21 2 | paused | 2024-05-03 12:00 | 24 2 | active | 2024-05-03 12:00 | 23 3 | closed | 2024-05-04 08:00 | 31 Output: account_id | status | changed_at | load_seq 1 | active | 2024-05-01 10:00 | 12 2 | paused | 2024-05-03 12:00 | 24 3 | closed | 2024-05-04 08:00 | 31 Account 1 has 2 rows at 10:00 and load_seq 12 wins; account 2 has 2 rows at 12:00 on 3 May and load_seq 24 (paused) beats 23 even though it arrived first.

Topics: lakebench, pandas, sort_values, tie-break, latest.

More interview problems · All interview problems · Learn data engineering