Coding questions · Instacart
Implement a pivot table transformation
This question evaluates competency in data transformation and aggregation, specifically pivot operations, time-based grouping, null handling, dimensionality reduction (top‑K/OTHER), scalability for large datasets, and expressing solutions in both Python (pandas or pure) and SQL within the Data Manipulation (SQL/Python) domain.
Given a dataset of transactions with columns: user_id (string), category (string), subcategory (string), amount (float), ts (ISO timestamp), implement a pivot table transformation: (a) rows = category; columns = month (YYYY-MM derived from ts); values = sum(amount), filling missing cells with 0; (b) rows = (user_id, category); columns = subcategory; values = count(*). Write a Python solution (pandas or pure Python), and outline an equivalent SQL approach. Discuss handling nulls, time zones, very large data (streaming/chunking), and limiting the number of columns (top-K with an OTHER bucket). State time and space complexity.
Pivot transaction amounts by category and month
You are given a transactions table with the following columns:
- user_id (string)
- category (string)
- subcategory (string)
- amount (decimal)
- ts (timestamp, stored in UTC)
Write a SQL query that produces a pivoted report where:
- Each row represents a category.
- There is one column per calendar month from January 2025 to March 2025.
- The value in each cell is the sum(amount) for that (category, month) combination.
- If a category has no transactions in a given month, the corresponding cell should be 0.
- Ignore rows where category IS NULL.
Name the monthly columns as amt_2025_01, amt_2025_02, and amt_2025_03.
Table transactions
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(20) | NOT NULL |
| category | VARCHAR(50) | |
| subcategory | VARCHAR(50) | |
| amount | DECIMAL(10,2) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
Sample data:
| amount | category | subcategory | ts | user_id |
|---|---|---|---|---|
| 20 | Food | Groceries | 2025-01-15 10:00:00 | u1 |
| 35.5 | Food | Restaurant | 2025-01-20 18:00:00 | u2 |
| 200 | Travel | Flight | 2025-02-05 09:30:00 | u1 |
| 15 | Food | Groceries | 2025-02-10 12:00:00 | u3 |
| 12 | Entertainment | Movies | 2025-02-12 20:00:00 | u2 |
| 150 | Travel | Hotel | 2025-03-03 15:00:00 | u1 |
| 25 | Entertainment | Games | 2025-03-08 21:00:00 | u3 |
| 50 | NULL | Other | 2025-02-15 13:00:00 | u4 |
| 8 | Entertainment | Movies | 2025-01-05 19:00:00 | u2 |
Expected output
One row per category with total amount per month from January to March 2025. Missing (category, month) combinations appear as 0.
| amt_2025_01 | amt_2025_02 | amt_2025_03 | category |
|---|---|---|---|
| 8 | 12 | 25 | Entertainment |
| 55.5 | 15 | 0 | Food |
| 0 | 200 | 150 | Travel |
Hints
- Use conditional aggregation with CASE expressions to turn rows into columns.
- Define one SUM(CASE ...) expression per month and group by category.
Pivot transaction counts by user, category, and subcategory
Using the same transactions table, write a SQL query that produces a pivoted report where:
- Each row represents a (user_id, category) pair.
- Each column represents a subcategory present in the data.
- The value in each cell is the count(*) of transactions for that (user_id, category, subcategory) combination.
- Treat NULL category values as rows to be ignored (do not include them in the output).
For the given sample data, produce columns for the subcategories: Groceries, Restaurant, Flight, Hotel, Movies, Games, and Other. Name the columns groceries_cnt, restaurant_cnt, flight_cnt, hotel_cnt, movies_cnt, games_cnt, and other_cnt.
Table transactions
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(20) | NOT NULL |
| category | VARCHAR(50) | |
| subcategory | VARCHAR(50) | |
| amount | DECIMAL(10,2) | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
Sample data:
| amount | category | subcategory | ts | user_id |
|---|---|---|---|---|
| 20 | Food | Groceries | 2025-01-15 10:00:00 | u1 |
| 35.5 | Food | Restaurant | 2025-01-20 18:00:00 | u2 |
| 200 | Travel | Flight | 2025-02-05 09:30:00 | u1 |
| 15 | Food | Groceries | 2025-02-10 12:00:00 | u3 |
| 12 | Entertainment | Movies | 2025-02-12 20:00:00 | u2 |
| 150 | Travel | Hotel | 2025-03-03 15:00:00 | u1 |
| 25 | Entertainment | Games | 2025-03-08 21:00:00 | u3 |
| 50 | NULL | Other | 2025-02-15 13:00:00 | u4 |
| 8 | Entertainment | Movies | 2025-01-05 19:00:00 | u2 |
Expected output
One row per (user_id, category) with a count of transactions for each subcategory. Only rows with non-NULL category are included.
| category | flight_cnt | games_cnt | groceries_cnt | hotel_cnt | movies_cnt | other_cnt | restaurant_cnt | user_id |
|---|---|---|---|---|---|---|---|---|
| Food | 0 | 0 | 1 | 0 | 0 | 0 | 0 | u1 |
| Travel | 1 | 0 | 0 | 1 | 0 | 0 | 0 | u1 |
| Entertainment | 0 | 0 | 0 | 0 | 2 | 0 | 0 | u2 |
| Food | 0 | 0 | 0 | 0 | 0 | 0 | 1 | u2 |
| Entertainment | 0 | 1 | 0 | 0 | 0 | 0 | 0 | u3 |
| Food | 0 | 0 | 1 | 0 | 0 | 0 | 0 | u3 |
Hints
- Use COUNT(CASE WHEN subcategory = 'X' THEN 1 END) to compute counts per subcategory.
- Group by both user_id and category to get one row per pair.