Coding questions · Instacart
Pivot transactions by date without date libs
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.
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.
Pivot 7-Day Shopper Transactions Without Date Functions
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_idd0= totalamounton '2025-05-25'd1= totalamounton '2025-05-26'd2= totalamounton '2025-05-27'd3= totalamounton '2025-05-28'd4= totalamounton '2025-05-29'd5= totalamounton '2025-05-30'd6= totalamounton '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.
Table transactions
| column | type | constraints |
|---|---|---|
| shopper_id | INT | NOT NULL |
| transaction_date | VARCHAR(10) | NOT NULL |
| amount | DECIMAL(10,2) | NOT NULL |
Sample data:
| amount | shopper_id | transaction_date |
|---|---|---|
| 20 | 1 | 2025-05-31 |
| 10 | 1 | 2025-05-25 |
| 8 | 2 | 2025-05-27 |
| 9 | 3 | 2025-05-30 |
| 3 | 1 | 2025-05-28 |
| 4.5 | 2 | 2025-05-29 |
| 7.5 | 1 | 2025-05-26 |
| 2 | 2 | 2025-05-27 |
| 1 | 3 | 2025-05-31 |
| 5 | 1 | 2025-05-25 |
| 100 | 1 | 2025-06-01 |
| 6 | 2 | 2025-05-24 |
Expected output
One row per shopper_id with at least one transaction between '2025-05-25' and '2025-05-31' (inclusive), pivoting the 7 days into columns d0..d6 and filling missing days with 0. Transactions outside this window are ignored.
| d0 | d1 | d2 | d3 | d4 | d5 | d6 | shopper_id |
|---|---|---|---|---|---|---|---|
| 15 | 7.5 | 0 | 3 | 0 | 0 | 20 | 1 |
| 0 | 0 | 10 | 0 | 4.5 | 0 | 0 | 2 |
| 0 | 0 | 0 | 0 | 0 | 9 | 1 | 3 |
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.