Transform retail data with pandas groupby/merge/concat
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using pandas only (groupby/agg/merge/concat; no for-loops), write code to answer the sub-questions below on the following small dataframes. Assume timestamps are UTC.
users
user_id | signup_date | region | device
U1 | 2025-08-28 | US | iOS
U2 | 2025-08-30 | US | Web
U3 | 2025-09-01 | CA | Android
U4 | 2025-09-02 | US | Web
orders
order_id | user_id | ts | amount | category
O1 | U1 | 2025-09-01 10:05 | 20.00 | Books
O2 | U2 | 2025-09-02 09:10 | 35.00 | Home
O3 | U1 | 2025-09-02 11:00 | 15.00 | Books
O4 | U3 | 2025-09-03 12:00 | 50.00 | Games
O5 | U2 | 2025-09-03 13:30 | 60.00 | Home
events
user_id | ts | event
U1 | 2025-09-01 09:00 | view
U1 | 2025-09-01 09:05 | add_to_cart
U2 | 2025-09-02 09:00 | view
U3 | 2025-09-03 11:50 | view
U3 | 2025-09-03 11:55 | view
U4 | 2025-09-03 15:00 | view
Tasks:
1) Daily Active Users (DAU) by device: compute DAU per calendar day using events, then compute a 2-day rolling unique user count per device (aligned to day end). Explain how you ensure uniqueness across days.
2) First order: left-join users to first order per user to produce first_order_ts and days_to_first_order (float days), with NaN for users without orders. Be careful about users who have same-day signup and order.
3) Top categories per user: compute each user’s top-2 categories by total spend (ties broken alphabetically), returning columns top_cat_1 and top_cat_2; users with <2 categories should have NaN for missing values.
4) Add an "ALL" summary row that aggregates overall revenue by day across all devices and concatenate it to the per-device daily revenue table (schema: day, device, revenue). Ensure consistent column types after concat.
For each step, provide pandas code, resulting schema, and explain time/memory complexity and edge cases (empty joins, duplicate events, differing time zones).
Overview: This question evaluates pandas-based data manipulation skills, specifically proficiency with groupby/agg, merge/concat, rolling-window and time-series aggregations, deduplication and tie-breaking logic for ranking and joins.
Read the full Amazon Data Scientist interview experience this question came from
Daily Active Users and 2-Day Rolling Unique Users by Device
Using the events and users tables, calculate Daily Active Users (DAU) by device and a 2-day rolling unique user count per device. A user is active on a given day if they generated at least one event on that calendar day. First compute DAU as the count of distinct users per (day, device). Then, for each (day, device), compute rolling_2d_dau as the number of distinct users on that device who were active on that day or the previous calendar day (a 2-day window ending on that day). Ensure that each user is counted at most once per device per 2-day window even if they were active on both days. Assume timestamps are stored in UTC and ignore timezone conversions. Return columns: day, device, dau, rolling_2d_dau.
Tables
users(user_id VARCHAR(10), signup_date DATE, region VARCHAR(5), device VARCHAR(20))
orders(order_id VARCHAR(10), user_id VARCHAR(10), ts TIMESTAMP, amount DECIMAL(10,2), category VARCHAR(50))
events(user_id VARCHAR(10), ts TIMESTAMP, event VARCHAR(50))
Hints
- First aggregate events to distinct (device, user_id, day) before counting.
- For the rolling 2-day metric, use a correlated subquery or self-join filtered to the current day and the previous day, and COUNT(DISTINCT user_id).
First Order Timestamp and Days from Signup
Using the users and orders tables, find each user's first order timestamp and the number of days between signup and that first order. For each user, identify the earliest order ts (first_order_ts). Left join this information to all users so that users without any orders still appear with NULL values for first_order_ts and days_to_first_order. Compute days_to_first_order as a fractional number of days, including time-of-day (for example, using the difference in seconds divided by 86,400). Be careful to handle users whose first order occurs on the same calendar day as their signup. Return columns: user_id, signup_date, region, device, first_order_ts formatted as `YYYY-MM-DD HH24:MI:SS`, and days_to_first_order.
Tables
users(user_id VARCHAR(10), signup_date DATE, region VARCHAR(5), device VARCHAR(20))
orders(order_id VARCHAR(10), user_id VARCHAR(10), ts TIMESTAMP, amount DECIMAL(10,2), category VARCHAR(50))
Hints
- Use `ROW_NUMBER` ordered by order timestamp to identify each user's first order.
- Left join first orders back to all users so users without orders remain in the output.
Top-2 Spending Categories per User
Using the orders table, compute each user’s top 2 categories by total spend. First aggregate total spend per (user_id, category). Then, for each user, rank categories by total_amount descending, breaking ties by category name in ascending alphabetical order. Finally, pivot the top 2 categories into columns top_cat_1 and top_cat_2. Users with fewer than 2 categories should have NULL in the missing positions, and users with no orders should have both top_cat_1 and top_cat_2 as NULL. Return columns: user_id, top_cat_1, top_cat_2, including all users.
Tables
users(user_id VARCHAR(10), signup_date DATE, region VARCHAR(5), device VARCHAR(20))
orders(order_id VARCHAR(10), user_id VARCHAR(10), ts TIMESTAMP, amount DECIMAL(10,2), category VARCHAR(50))
Hints
- First SUM amount grouped by user_id and category to get total spend per category.
- Use a window function to rank categories per user, then pivot rn = 1 and rn = 2 into columns with conditional aggregation.
Daily Revenue per Device with an ALL Summary Row
From the users and orders tables, compute a daily revenue table with schema (day, device, revenue), where revenue is the total order amount for that day and device. Then add summary rows where device = 'ALL' that contain the total revenue per day across all devices. Concatenate the per-device rows and the 'ALL' summary rows into a single result set, ensuring that column types are consistent (day as DATE, device as VARCHAR, revenue as DECIMAL). Return columns: day, device, revenue.
Tables
users(user_id VARCHAR(10), signup_date DATE, region VARCHAR(5), device VARCHAR(20))
orders(order_id VARCHAR(10), user_id VARCHAR(10), ts TIMESTAMP, amount DECIMAL(10,2), category VARCHAR(50))
Hints
- First join orders to users to get the device, then aggregate SUM(amount) by date and device.
- Use UNION ALL to append a second aggregation where device is set to 'ALL' and you group only by date.