cswork / libraryOpen practice workspace

Coding questions · Amazon

Manipulate time-series with Pandas groupby

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.

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).

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.

Table events

columntypeconstraints
event_idINTPRIMARY KEY
user_idINTNOT NULL
event_typeVARCHAR(20)NOT NULL
ts_utcTIMESTAMP WITH TIME ZONENOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

event_idevent_typerevenuets_utcuser_id
1view02025-03-07 08:00:00+001
2purchase202025-03-07 09:00:00+001
3view02025-03-08 18:00:00+002
4signup02025-03-09 00:30:00+003
5view02025-03-09 10:00:00+001
6purchase352025-03-10 02:00:00+002
7view02025-03-11 16:00:00+003
8signup02025-03-11 20:00:00+004
9view02025-03-13 23:00:00+004
10view02025-03-14 04:00:00+002
11purchase152025-03-15 12:00:00+001
12purchase502025-03-16 03:00:00+003

Expected output

All events with their UTC timestamp, converted America/Los_Angeles local timestamp (handling DST), and the local calendar date. Ordered by event_id.

event_date_pacificevent_idevent_typerevenuets_pacificts_utcuser_id
2025-03-071view0.002025-03-07 00:00:002025-03-07 08:00:00+001
2025-03-072purchase20.002025-03-07 01:00:002025-03-07 09:00:00+001
2025-03-083view0.002025-03-08 10:00:002025-03-08 18:00:00+002
2025-03-084signup0.002025-03-08 16:30:002025-03-09 00:30:00+003
2025-03-095view0.002025-03-09 03:00:002025-03-09 10:00:00+001
2025-03-096purchase35.002025-03-09 19:00:002025-03-10 02:00:00+002
2025-03-117view0.002025-03-11 09:00:002025-03-11 16:00:00+003
2025-03-118signup0.002025-03-11 13:00:002025-03-11 20:00:00+004
2025-03-139view0.002025-03-13 16:00:002025-03-13 23:00:00+004
2025-03-1310view0.002025-03-13 21:00:002025-03-14 04:00:00+002
2025-03-1511purchase15.002025-03-15 05:00:002025-03-15 12:00:00+001
2025-03-1512purchase50.002025-03-15 20:00:002025-03-16 03:00:00+003

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.
  3. Compute the Pacific date after converting the timestamp to America/Los_Angeles.

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.

Table events

columntypeconstraints
event_idINTPRIMARY KEY
user_idINTNOT NULL
event_typeVARCHAR(20)NOT NULL
ts_utcTIMESTAMP WITH TIME ZONENOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

event_idevent_typerevenuets_utcuser_id
1view02025-03-07 08:00:00+001
2purchase202025-03-07 09:00:00+001
3view02025-03-08 18:00:00+002
4signup02025-03-09 00:30:00+003
5view02025-03-09 10:00:00+001
6purchase352025-03-10 02:00:00+002
7view02025-03-11 16:00:00+003
8signup02025-03-11 20:00:00+004
9view02025-03-13 23:00:00+004
10view02025-03-14 04:00:00+002
11purchase152025-03-15 12:00:00+001
12purchase502025-03-16 03:00:00+003

Expected output

One row per America/Los_Angeles local date that has at least one event, with daily active users (distinct users) and a 7-day moving average over the previous 6 days plus the current day.

daudau_7d_moving_avgevent_date_pacific
112025-03-07
21.52025-03-08
21.66672025-03-09
21.752025-03-11
21.82025-03-13
222025-03-15

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.

Table events

columntypeconstraints
event_idINTPRIMARY KEY
user_idINTNOT NULL
event_typeVARCHAR(20)NOT NULL
ts_utcTIMESTAMP WITH TIME ZONENOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

event_idevent_typerevenuets_utcuser_id
1view02025-03-07 08:00:00+001
2purchase202025-03-07 09:00:00+001
3view02025-03-08 18:00:00+002
4signup02025-03-09 00:30:00+003
5view02025-03-09 10:00:00+001
6purchase352025-03-10 02:00:00+002
7view02025-03-11 16:00:00+003
8signup02025-03-11 20:00:00+004
9view02025-03-13 23:00:00+004
10view02025-03-14 04:00:00+002
11purchase152025-03-15 12:00:00+001
12purchase502025-03-16 03:00:00+003

Expected output

All events with a 7-day rolling count per (user_id, event_type), ordered by user_id, event_type, ts_utc.

event_idevent_typerevenuerolling_7d_countts_utcuser_id
2purchase20.0012025-03-07 09:00:001
11purchase15.0012025-03-15 12:00:001
1view0.0012025-03-07 08:00:001
5view0.0022025-03-09 10:00:001
6purchase35.0012025-03-10 02:00:002
3view0.0012025-03-08 18:00:002
10view0.0022025-03-14 04:00:002
12purchase50.0012025-03-16 03:00:003
4signup0.0012025-03-09 00:30:003
7view0.0012025-03-11 16:00:003
8signup0.0012025-03-11 20:00:004
9view0.0012025-03-13 23:00:004

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.

Table events

columntypeconstraints
event_idINTPRIMARY KEY
user_idINTNOT NULL
event_typeVARCHAR(20)NOT NULL
ts_utcTIMESTAMP WITH TIME ZONENOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

event_idevent_typerevenuets_utcuser_id
1view02025-03-07 08:00:00+001
2purchase202025-03-07 09:00:00+001
3view02025-03-08 18:00:00+002
4signup02025-03-09 00:30:00+003
5view02025-03-09 10:00:00+001
6purchase352025-03-10 02:00:00+002
7view02025-03-11 16:00:00+003
8signup02025-03-11 20:00:00+004
9view02025-03-13 23:00:00+004
10view02025-03-14 04:00:00+002
11purchase152025-03-15 12:00:00+001
12purchase502025-03-16 03:00:00+003

Expected output

Weekly retention from week w to week w+1 based on America/Los_Angeles local weeks starting on Monday. Only weeks that have a subsequent active week are included.

cohort_usersretention_ratereturning_usersweek_start
3132025-03-03

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.

Table events

columntypeconstraints
event_idINTPRIMARY KEY
user_idINTNOT NULL
event_typeVARCHAR(20)NOT NULL
ts_utcTIMESTAMP WITH TIME ZONENOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

event_idevent_typerevenuets_utcuser_id
1view02025-03-07 08:00:00+001
2purchase202025-03-07 09:00:00+001
3view02025-03-08 18:00:00+002
4signup02025-03-09 00:30:00+003
5view02025-03-09 10:00:00+001
6purchase352025-03-10 02:00:00+002
7view02025-03-11 16:00:00+003
8signup02025-03-11 20:00:00+004
9view02025-03-13 23:00:00+004
10view02025-03-14 04:00:00+002
11purchase152025-03-15 12:00:00+001
12purchase502025-03-16 03:00:00+003

Expected output

Continuous daily series from min to max local event date in America/Los_Angeles, with DAU per day; missing dates have DAU = 0.

dauevent_date_pacific
12025-03-07
22025-03-08
22025-03-09
02025-03-10
22025-03-11
02025-03-12
22025-03-13
02025-03-14
22025-03-15

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.