Coding questions · Amazon
Transform event logs with subscription windows in pandas
This question evaluates a candidate's competency in time-based event attribution and temporal interval manipulation using pandas, covering interval merging, subscription-aligned revenue aggregation, anomaly flagging, and handling edge cases like overlapping or back-to-back subscriptions.
Using pandas, compute user-level subscription-aligned revenue and anomalies for September 2025. DataFrames: events(user_id:int, ts:UTC datetime, event:str in {'view','add_to_cart','purchase'}, product_id:int, price_usd:float), subs(user_id:int, plan:str, start_ts:UTC datetime, end_ts:UTC datetime or NaT). Requirements: (1) For each user, compute active_subscription_days in 2025-09 and total purchase revenue that occurred while the user was actively subscribed (purchase ts ∈ [start_ts, end_ts)); (2) Flag purchases outside any active window; (3) If a user has overlapping or back-to-back subscriptions, merge them into minimal disjoint half-open intervals before attribution; (4) Output two DataFrames: user_month_agg(user_id, month='2025-09', active_subscription_days:int, subscribed_purchase_revenue:float, out_of_window_purchases:int) and anomalies(user_id, ts, price_usd, reason='outside_window'|'overlap_fixed'); (5) Solve with vectorized operations (e.g., IntervalIndex, merge_asof, or interval trees) and discuss scalability to 100M events with limited RAM (chunking, dtype optimization, parquet scans). Small sample: subs:
(1,'pro','2025-08-28T00:00Z','2025-09-10T00:00Z')
(1,'pro','2025-09-10T00:00Z','2025-10-10T00:00Z')
(2,'basic','2025-09-05T12:00Z',NaT)
events:
(1,'2025-09-09T22:00Z','purchase',101,19.99)
(1,'2025-09-15T03:00Z','purchase',102,5.00)
(2,'2025-09-01T01:00Z','purchase',103,9.99)
Subscription-aligned monthly revenue and anomalies for September 2025
You are given events and subscription intervals for September 2025.
Write one PostgreSQL query that returns a single labeled result table containing both:
- user_month_agg: one row per user with active_subscription_days, subscribed_purchase_revenue, and out_of_window_purchases for September 2025.
- anomalies: purchases in September that either occurred outside any merged subscription window or were covered by overlapping raw subscription intervals.
Treat subscription intervals as half-open [start_ts, end_ts). NULL end_ts means the subscription continues indefinitely. Merge overlapping or adjacent intervals per user before counting active days and classifying in-window purchases. Return all rows in one output with a result_set column. Columns that do not apply to a result_set should be NULL.
Table subs
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| plan | VARCHAR(20) | NOT NULL |
| start_ts | TIMESTAMP | NOT NULL |
| end_ts | TIMESTAMP |
Sample data:
| end_ts | plan | start_ts | user_id |
|---|---|---|---|
| 2025-09-10 00:00:00 | pro | 2025-08-28 00:00:00 | 1 |
| 2025-10-10 00:00:00 | pro | 2025-09-10 00:00:00 | 1 |
| NULL | basic | 2025-09-05 12:00:00 | 2 |
| 2025-09-10 00:00:00 | basic | 2025-08-30 00:00:00 | 3 |
| 2025-09-20 00:00:00 | pro | 2025-09-05 00:00:00 | 3 |
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| event | VARCHAR(20) | NOT NULL |
| product_id | INT | |
| price_usd | DECIMAL(10,2) |
Sample data:
| event | price_usd | product_id | ts | user_id |
|---|---|---|---|---|
| purchase | 19.99 | 101 | 2025-09-09 22:00:00 | 1 |
| purchase | 5 | 102 | 2025-09-15 03:00:00 | 1 |
| view | 19.99 | 101 | 2025-08-20 12:00:00 | 1 |
| purchase | 9.99 | 103 | 2025-09-01 01:00:00 | 2 |
| purchase | 4.5 | 104 | 2025-09-06 10:00:00 | 2 |
| view | 4.5 | 104 | 2025-09-10 09:00:00 | 2 |
| purchase | 12 | 106 | 2025-09-03 08:00:00 | 3 |
| purchase | 20 | 107 | 2025-09-07 12:00:00 | 3 |
| purchase | 8 | 108 | 2025-09-25 18:00:00 | 3 |
| purchase | 7 | 105 | 2025-08-29 09:00:00 | 3 |
| purchase | 15 | 109 | 2025-09-10 11:00:00 | 4 |
| view | 15 | 110 | 2025-09-20 09:30:00 | 4 |
Expected output
Sample output for the single labeled result table.
| active_subscription_days | month | out_of_window_purchases | price_usd | reason | result_set | subscribed_purchase_revenue | ts | user_id |
|---|---|---|---|---|---|---|---|---|
| 30 | 2025-09 | 0 | NULL | NULL | user_month_agg | 24.99 | NULL | 1 |
| 26 | 2025-09 | 1 | NULL | NULL | user_month_agg | 4.50 | NULL | 2 |
| 19 | 2025-09 | 1 | NULL | NULL | user_month_agg | 32.00 | NULL | 3 |
| 0 | 2025-09 | 1 | NULL | NULL | user_month_agg | 0 | NULL | 4 |
| NULL | NULL | NULL | 9.99 | outside_window | anomalies | NULL | 2025-09-01 01:00:00 | 2 |
| NULL | NULL | NULL | 20.00 | overlap_fixed | anomalies | NULL | 2025-09-07 12:00:00 | 3 |
| NULL | NULL | NULL | 8.00 | outside_window | anomalies | NULL | 2025-09-25 18:00:00 | 3 |
| NULL | NULL | NULL | 15.00 | outside_window | anomalies | NULL | 2025-09-10 11:00:00 | 4 |
Hints
- Merge intervals with a running max of prior end_ts.
- Use raw interval overlap counts to detect overlap anomalies after normalization.
- Union aggregate rows and anomaly rows into one result table.