Latest price per product and store

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

Keep the most recent price for every product and store pair. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

df holds price changes with product_id, store_id, price and updated_at (a date written as text like 2024-03-01, which sorts in time order). A product can have a different price in each store. Return the current price for each product in each store: the row with the latest updated_at for that pair. Keep all 4 columns, sort by product_id, then store_id, and reset the index. Assign the DataFrame to result.

Requirements

• Return the 4 columns of df.
• Sort by product_id, then store_id, and reset the index.

Constraints

• updated_at strings sort in time order.
• No 2 rows of one product and store share an updated_at.

Examples

Input: df product_id | store_id | price | updated_at 1 | N | 9.5 | 2024-03-01 1 | S | 9 | 2024-03-05 2 | N | 4 | 2024-02-10 1 | N | 10 | 2024-04-01 2 | S | 4.5 | 2024-03-20 2 | N | 4.25 | 2024-03-30 Output: product_id | store_id | price | updated_at 1 | N | 10 | 2024-04-01 1 | S | 9 | 2024-03-05 2 | N | 4.25 | 2024-03-30 2 | S | 4.5 | 2024-03-20 Product 1 in store N is now 10.0 (April), in store S 9.0; product 2 in store N is 4.25 (the March change beats February) and in store S 4.5.

Topics: lakebench, pandas, drop_duplicates, composite key, latest.

More interview problems · All interview problems · Learn data engineering