Amazon·Software Engineer·Technical Phone Screen
- Given a DataFrame with columns user_id, event_type, ts_utc, and revenue, parse ts_utc as a timezone-aware timestamp and convert it to America/Los_Angeles while correctly handling DST transitions.
- Using the same DataFrame, compute daily active users and then apply a 7-day moving average over that DAU series.
- For each combination of user and event_type, compute a 7-day rolling count of events without using any explicit Python loops.
- Compute weekly user retention: for each week w, find how many users who were active in week w also appear in week w+1, and express that as both a count and a rate.
- After all the aggregations, resample the resulting time series to fill in any missing calendar dates with zeros rather than NaN.
“I knew the broad strokes but fumbled the exact Pandas call for ambiguous times during the fall-back hour.”