Coding questions · Amazon
Transform retail data with pandas groupby/merge/concat
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.
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:
- 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.
- 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.
- 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.
- 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).
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.
Table users
| column | type | constraints |
|---|
| user_id | VARCHAR(10) | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
| region | VARCHAR(5) | NOT NULL |
| device | VARCHAR(20) | NOT NULL |
Sample data:
| device | region | signup_date | user_id |
|---|
| iOS | US | 2025-08-28 | U1 |
| Web | US | 2025-08-30 | U2 |
| Android | CA | 2025-09-01 | U3 |
| Web | US | 2025-09-02 | U4 |
Table orders
| column | type | constraints |
|---|
| order_id | VARCHAR(10) | PRIMARY KEY |
| user_id | VARCHAR(10) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
Sample data:
| amount | category | order_id | ts | user_id |
|---|
| 20 | Books | O1 | 2025-09-01 10:05:00 | U1 |
| 35 | Home | O2 | 2025-09-02 09:10:00 | U2 |
| 15 | Books | O3 | 2025-09-02 11:00:00 | U1 |
| 50 | Games | O4 | 2025-09-03 12:00:00 | U3 |
| 60 | Home | O5 | 2025-09-03 13:30:00 | U2 |
Table events
| column | type | constraints |
|---|
| user_id | VARCHAR(10) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| event | VARCHAR(50) | NOT NULL |
Sample data:
| event | ts | user_id |
|---|
| view | 2025-09-01 09:00:00 | U1 |
| add_to_cart | 2025-09-01 09:05:00 | U1 |
| view | 2025-09-02 09:00:00 | U2 |
| view | 2025-09-03 11:50:00 | U3 |
| view | 2025-09-03 11:55:00 | U3 |
| view | 2025-09-03 15:00:00 | U4 |
Expected output
For each calendar day and device with at least one event, return the distinct user count on that day (dau) and the distinct user count over the 2-day window ending on that day for that device (rolling_2d_dau).
| dau | day | device | rolling_2d_dau |
|---|
| 1 | 2025-09-01 | iOS | 1 |
| 1 | 2025-09-02 | Web | 1 |
| 1 | 2025-09-03 | Android | 1 |
| 1 | 2025-09-03 | Web | 2 |
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.
Table users
| column | type | constraints |
|---|
| user_id | VARCHAR(10) | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
| region | VARCHAR(5) | NOT NULL |
| device | VARCHAR(20) | NOT NULL |
Sample data:
| device | region | signup_date | user_id |
|---|
| iOS | US | 2025-08-28 | U1 |
| Web | US | 2025-08-30 | U2 |
| Android | CA | 2025-09-01 | U3 |
| Web | US | 2025-09-02 | U4 |
Table orders
| column | type | constraints |
|---|
| order_id | VARCHAR(10) | PRIMARY KEY |
| user_id | VARCHAR(10) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
Sample data:
| amount | category | order_id | ts | user_id |
|---|
| 20 | Books | O1 | 2025-09-01 10:05:00 | U1 |
| 35 | Home | O2 | 2025-09-02 09:10:00 | U2 |
| 15 | Books | O3 | 2025-09-02 11:00:00 | U1 |
| 50 | Games | O4 | 2025-09-03 12:00:00 | U3 |
| 60 | Home | O5 | 2025-09-03 13:30:00 | U2 |
Expected output
Each user with their signup information, the timestamp of their first order if any, and the fractional number of days from signup to that first order. Users without orders have NULLs for the last two columns.
| days_to_first_order | device | first_order_ts | region | signup_date | user_id |
|---|
| 4.420139 | iOS | 2025-09-01 10:05:00 | US | 2025-08-28 | U1 |
| 3.381944 | Web | 2025-09-02 09:10:00 | US | 2025-08-30 | U2 |
| 2.5 | Android | 2025-09-03 12:00:00 | CA | 2025-09-01 | U3 |
| NULL | Web | NULL | US | 2025-09-02 | U4 |
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.
- Use
EXTRACT(EPOCH FROM interval) / 86400.0 for fractional days and TO_CHAR for timestamp display.
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.
Table users
| column | type | constraints |
|---|
| user_id | VARCHAR(10) | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
| region | VARCHAR(5) | NOT NULL |
| device | VARCHAR(20) | NOT NULL |
Sample data:
| device | region | signup_date | user_id |
|---|
| iOS | US | 2025-08-28 | U1 |
| Web | US | 2025-08-30 | U2 |
| Android | CA | 2025-09-01 | U3 |
| Web | US | 2025-09-02 | U4 |
Table orders
| column | type | constraints |
|---|
| order_id | VARCHAR(10) | PRIMARY KEY |
| user_id | VARCHAR(10) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
Sample data:
| amount | category | order_id | ts | user_id |
|---|
| 20 | Books | O1 | 2025-09-01 10:05:00 | U1 |
| 35 | Home | O2 | 2025-09-02 09:10:00 | U2 |
| 15 | Books | O3 | 2025-09-02 11:00:00 | U1 |
| 50 | Games | O4 | 2025-09-03 12:00:00 | U3 |
| 60 | Home | O5 | 2025-09-03 13:30:00 | U2 |
Expected output
Each user with their top 1 and top 2 spending categories by total amount. Users with fewer than two categories have NULLs for missing top categories.
| top_cat_1 | top_cat_2 | user_id |
|---|
| Books | NULL | U1 |
| Home | NULL | U2 |
| Games | NULL | U3 |
| NULL | NULL | U4 |
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.
Table users
| column | type | constraints |
|---|
| user_id | VARCHAR(10) | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
| region | VARCHAR(5) | NOT NULL |
| device | VARCHAR(20) | NOT NULL |
Sample data:
| device | region | signup_date | user_id |
|---|
| iOS | US | 2025-08-28 | U1 |
| Web | US | 2025-08-30 | U2 |
| Android | CA | 2025-09-01 | U3 |
| Web | US | 2025-09-02 | U4 |
Table orders
| column | type | constraints |
|---|
| order_id | VARCHAR(10) | PRIMARY KEY |
| user_id | VARCHAR(10) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
| category | VARCHAR(50) | NOT NULL |
Sample data:
| amount | category | order_id | ts | user_id |
|---|
| 20 | Books | O1 | 2025-09-01 10:05:00 | U1 |
| 35 | Home | O2 | 2025-09-02 09:10:00 | U2 |
| 15 | Books | O3 | 2025-09-02 11:00:00 | U1 |
| 50 | Games | O4 | 2025-09-03 12:00:00 | U3 |
| 60 | Home | O5 | 2025-09-03 13:30:00 | U2 |
Expected output
Daily revenue per device plus an 'ALL' row per day with total revenue across all devices.
| day | device | revenue |
|---|
| 2025-09-01 | iOS | 20.00 |
| 2025-09-01 | ALL | 20.00 |
| 2025-09-02 | iOS | 15.00 |
| 2025-09-02 | Web | 35.00 |
| 2025-09-02 | ALL | 50.00 |
| 2025-09-03 | Android | 50.00 |
| 2025-09-03 | Web | 60.00 |
| 2025-09-03 | ALL | 110.00 |
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.