Coding questions · Instacart
Pivot data without date libraries
This question evaluates a candidate's competency in data manipulation and transformation, specifically pivoting tall datasets into wide views, handling missing metric values, ordering ISO date strings via string operations, and meeting algorithmic efficiency constraints (O(n log n)).
Transform a tall dataset into a wide, pivoted view without using any date/time libraries. Input: a list of records (date, store_id, metric, value) where date is an ISO 8601 string YYYY-MM-DD, metric ∈ {"sales", "refunds", "visits"}, and value is a non-negative integer. Output: for each (store_id, date), produce one row with columns [store_id, date, sales, refunds, visits], filling missing metric values with 0 and ordering rows by ascending date within each store. Requirements: (1) Implement this in code (any language), but you may not import date/time packages; rely only on string operations for ordering dates. (2) Ensure the solution is O(n log n) or better with respect to the number of records n. (3) Explain how you would handle malformed dates, duplicate (store_id, date, metric) entries, and very large inputs that do not fit in memory.
Pivot tall store metrics into wide daily rows using only ISO date strings
You are given a tall table of store metrics with columns (date_str, store_id, metric, value). The date is stored as an ISO 8601 string in the format YYYY-MM-DD (not a DATE type). The metric is one of: 'sales', 'refunds', 'visits'.
Write a single SQL query that produces a wide, pivoted result with one row per (store_id, date_str) and columns: [store_id, date_str, sales, refunds, visits]
Rules/requirements:
- Do NOT cast date_str to a DATE type or use date/time functions. Rely on the ISO string format for ordering.
- Fill missing metrics with 0.
- If there are duplicate rows for the same (store_id, date_str, metric), aggregate them by summing value.
- Exclude malformed date strings. For this question, treat a date as valid only if:
- it matches the pattern 'YYYY-MM-DD' (length 10 with '-' in positions 5 and 8),
- month is between '01' and '12', and day is between '01' and '31' (string comparisons are acceptable).
- Order the final output by store_id ascending, then date_str ascending (which will also be chronological because of ISO formatting).
Table store_metric_events
| column | type | constraints |
|---|---|---|
| event_id | INT | PRIMARY KEY, NOT NULL |
| date_str | VARCHAR(10) | NOT NULL |
| store_id | INT | NOT NULL |
| metric | VARCHAR(10) | NOT NULL |
| value | INT | NOT NULL |
Sample data:
| date_str | event_id | metric | store_id | value |
|---|---|---|---|---|
| 2025-05-30 | 1 | sales | 1 | 100 |
| 2025-05-30 | 2 | refunds | 1 | 5 |
| 2025-05-30 | 3 | visits | 1 | 50 |
| 2025-05-31 | 4 | sales | 1 | 120 |
| 2025-05-31 | 5 | sales | 1 | 30 |
| 2025-05-31 | 6 | visits | 1 | 60 |
| 2025-05-30 | 7 | visits | 2 | 40 |
| 2025-06-01 | 8 | sales | 2 | 200 |
| 2025-06-01 | 9 | refunds | 2 | 3 |
| 2025-5-29 | 10 | sales | 1 | 10 |
| 2025-13-01 | 11 | visits | 2 | 10 |
Expected output
One row per (store_id, date_str) with pivoted metric columns. Missing metrics are 0. Duplicate (store_id, date_str, metric) values are summed. Rows with malformed date_str are excluded. Ordered by store_id then date_str ascending.
| date_str | refunds | sales | store_id | visits |
|---|---|---|---|---|
| 2025-05-30 | 5 | 100 | 1 | 50 |
| 2025-05-31 | 0 | 150 | 1 | 60 |
| 2025-05-30 | 0 | 0 | 2 | 40 |
| 2025-06-01 | 3 | 200 | 2 | 0 |
Hints
- Because dates are in ISO format (YYYY-MM-DD), string sorting matches chronological sorting.
- Use conditional aggregation (SUM with CASE WHEN) to pivot the metric rows into columns.
- To handle duplicates for the same (store_id, date_str, metric), aggregate with SUM rather than MAX.