Quick Overview

This question evaluates proficiency in time-series data manipulation, including timezone-aware datetime parsing and DST handling, groupby and rolling-window aggregations, resampling to fill calendar gaps, and user-level retention and DAU calculations using idiomatic, vectorized Pandas or SQL operations.

Manipulate time-series with Pandas groupby

Company: Amazon

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Given a DataFrame events(user_id, event_type, ts_utc, revenue): 1) Parse ts_utc as timezone-aware, convert to America/Los_Angeles, and handle DST transitions. 2) Compute daily active users (DAU) and a 7-day moving average. 3) For each user and event_type, compute a 7-day rolling count. 4) Produce weekly retention: the number and rate of users active in week w who return in week w+1. 5) Resample to fill missing calendar dates with zeros. Provide idiomatic, vectorized Pandas code (no explicit Python loops).

Overview: This question evaluates proficiency in time-series data manipulation, including timezone-aware datetime parsing and DST handling, groupby and rolling-window aggregations, resampling to fill calendar gaps, and user-level retention and DAU calculations using idiomatic, vectorized Pandas or SQL operations.

Read the full Amazon Software Engineer interview experience this question came from

Convert UTC event timestamps to America/Los_Angeles with DST handling

You are given an `events` table where `ts_utc` stores event timestamps in UTC as `TIMESTAMP WITH TIME ZONE` values. Write a PostgreSQL query that returns every event ordered by `event_id`, including the original event fields plus: - `ts_utc`: the UTC timestamp rendered as `YYYY-MM-DD HH24:MI:SS+00` - `ts_pacific`: the same instant rendered as local `America/Los_Angeles` time in `YYYY-MM-DD HH24:MI:SS` format - `event_date_pacific`: the local Pacific calendar date in `YYYY-MM-DD` format Use PostgreSQL time zone conversion so daylight saving time transitions are handled by the database.

Tables

events(event_id INT, user_id INT, event_type VARCHAR(20), ts_utc TIMESTAMP WITH TIME ZONE, revenue DECIMAL(10,2))

Hints

  1. Use PostgreSQL `AT TIME ZONE` to convert a `TIMESTAMP WITH TIME ZONE` value to local wall-clock time.
  2. Format timestamps with `TO_CHAR`; DuckDB-style `strftime` is not valid PostgreSQL.

Daily active users and 7-day moving average

Using the events table, treat ts_utc as a UTC timestamp and convert it to America/Los_Angeles local time. Based on the local calendar date (event_date_pacific), compute: 1) Daily active users (DAU) as the count of distinct user_id per event_date_pacific. 2) A 7-day moving average of DAU, where the window covers the current day and the previous 6 calendar days (using the local date). Return event_date_pacific, dau, and dau_7d_moving_avg, ordered by event_date_pacific.

Tables

events(event_id INT, user_id INT, event_type VARCHAR(20), ts_utc TIMESTAMP WITH TIME ZONE, revenue DECIMAL(10,2))

Hints

  1. First convert ts_utc to a Pacific local DATE and aggregate distinct users per day.
  2. Use a window function with RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW to compute the 7-day moving average.

7-day rolling event count per user and event_type

Write a PostgreSQL query. Using the events table, compute for each row a 7-day rolling count of events for that user_id and event_type. The 7-day window is defined on ts_utc as the current event and all events for the same (user_id, event_type) that occurred in the previous 6 days. Return event_id, user_id, event_type, ts_utc formatted in UTC as 'YYYY-MM-DD HH24:MI:SS', revenue, and rolling_7d_count, ordered by user_id, event_type, and ts_utc.

Tables

events(event_id INT, user_id INT, event_type VARCHAR(20), ts_utc TIMESTAMP WITH TIME ZONE, revenue DECIMAL(10,2))

Hints

  1. Partition by user_id and event_type and order by ts_utc to define the rolling window.
  2. Use a window COUNT with RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW to cover a 7-day period.

Weekly retention: users active in week w who return in week w+1

Define weeks using the America/Los_Angeles local calendar, with weeks starting on Monday (i.e., use date_trunc('week', local_date)). A user is considered active in a week if they have at least one event in that week. Using the events table: 1) Convert ts_utc to America/Los_Angeles local date. 2) For each week w, compute cohort_users: the number of distinct users active in week w. 3) For each week w that has a following week w+1 with any activity, compute returning_users: the number of users who are active in both week w and week w+1. 4) Compute retention_rate = returning_users / cohort_users. Return week_start (DATE for week w, truncated to Monday), cohort_users, returning_users, and retention_rate, ordered by week_start.

Tables

events(event_id INT, user_id INT, event_type VARCHAR(20), ts_utc TIMESTAMP WITH TIME ZONE, revenue DECIMAL(10,2))

Hints

  1. First derive user-week activity using date_trunc('week', local_date).
  2. Compute cohorts per week and then self-join user_weeks to itself shifted by 7 days to find users active in consecutive weeks.

Resample events to daily calendar and fill missing dates with zeros

Using the events table, consider America/Los_Angeles local calendar dates derived from ts_utc. Generate a continuous sequence of local dates from the minimum to the maximum event_date_pacific, and for each date compute the number of distinct active users (DAU). For dates with no events, DAU should be 0. Return event_date_pacific and dau for every date in this range, ordered by event_date_pacific.

Tables

events(event_id INT, user_id INT, event_type VARCHAR(20), ts_utc TIMESTAMP WITH TIME ZONE, revenue DECIMAL(10,2))

Hints

  1. Convert ts_utc to a Pacific local DATE and aggregate distinct users per day.
  2. Use generate_series over the min and max dates, then LEFT JOIN to the daily aggregates and COALESCE nulls to zero.

Loading coding console...