Pandas interview question: UTC timestamps to local dates
Problem
Shared data rulesThe dashboard dates orders in UTC, but Finance uses its own time zone. For each finance date Sep 1–27, 2026, count delivered orders. Also find the share of them that the dashboard puts on another date.
Example
orders| order_id | status | created_at | Row note |
|---|---|---|---|
| 101 | delivered | 2026-09-10 15:00 | |
| 102 | delivered | 2026-09-11 01:30 | ← 21:30 in New York — Sep 10 |
| 103 | delivered | 2026-09-11 04:10 | ← 00:10 in New York — Sep 11 |
| 104 | delivered | 2026-09-11 18:00 | |
| 105 | delivered | 2026-09-12 02:00 | ← 22:00 in New York — Sep 11 |
- Order 102: 21:30 in New York — Sep 10
- Order 103: 00:10 in New York — Sep 11
- Order 105: 22:00 in New York — Sep 11
| finance_date | orders | shifted_orders | shifted_share |
|---|---|---|---|
| 2026-09-10 | 2 | 1 | 0.5 |
| 2026-09-11 | 3 | 1 | 0.3333 |
Finance’s Sep 10 has orders 101 and 102, and the dashboard puts 102 on Sep 11. Sep 11 has orders 103–105, and 105 lands on Sep 12.
Output columns
shifted_orders- how many of them the dashboard puts on another date
Notes
- Exception: Finance dates orders by New York time (ET, UTC−4 in summer).
Solve it free
Your code runs on real pandas in your browser. No sign-up needed.