Pivot transactions by date without date libs
Company: Instacart
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Given a stream of transaction rows (shopper_id, date_str, amount) where date_str is ISO format 'YYYY-MM-DD', produce a pivoted report for a specified 7‑day window [start_date, end_date]. For each shopper_id, output one row with columns shopper_id, d0, d1, ..., d6 where di is the sum of amount on the i‑th day offset from start_date. Constraints: rows may be unsorted and duplicate; you cannot import date/time libraries—treat date_str lexicographically for ordering and use simple arithmetic over precomputed consecutive ISO strings; assume all dates in the window exist. Specify your data structures, handling of missing days (fill with
0), and time/space complexity. Implement in any language.
Overview: This question evaluates proficiency in data manipulation, string-based date handling, aggregation and pivot transformations, along with reasoning about data structures, de-duplication and missing-value filling.
Read the full Instacart Software Engineer interview experience this question came from
You are given a table `transactions` with shopper transaction data. The column `transaction_date` is stored as an ISO string in the format 'YYYY-MM-DD' (not as a DATE type). Rows may be unsorted and may contain duplicate entries for the same shopper on the same day. For the fixed 7-day window from '2025-05-25' (start_date) to '2025-05-31' (end_date), write an SQL query to produce a pivoted report.
For each `shopper_id` that has at least one transaction within this 7-day window, output one row with the following columns:
- `shopper_id`
- `d0` = total `amount` on '2025-05-25'
- `d1` = total `amount` on '2025-05-26'
- `d2` = total `amount` on '2025-05-27'
- `d3` = total `amount` on '2025-05-28'
- `d4` = total `amount` on '2025-05-29'
- `d5` = total `amount` on '2025-05-30'
- `d6` = total `amount` on '2025-05-31'
If a shopper has no transactions on a particular day in this window, the corresponding `di` value should be 0. You may **not** use any date/time functions or casts; treat `transaction_date` purely as a string and rely on its lexicographic ordering and equality.
Write a single SQL query that returns this pivoted 7-day summary.
Tables
transactions(shopper_id INT, transaction_date VARCHAR(10), amount DECIMAL(10,2))
Hints
- First filter to the 7-day window using string comparison on transaction_date (BETWEEN '2025-05-25' AND '2025-05-31').
- Use conditional aggregation: SUM(CASE WHEN transaction_date = 'YYYY-MM-DD' THEN amount ELSE 0 END) to build each dx column.