Pivot data without date libraries
Company: Instacart
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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)).
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:
1) Do NOT cast date_str to a DATE type or use date/time functions. Rely on the ISO string format for ordering.
2) Fill missing metrics with 0.
3) If there are duplicate rows for the same (store_id, date_str, metric), aggregate them by summing value.
4) 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).
5) Order the final output by store_id ascending, then date_str ascending (which will also be chronological because of ISO formatting).
Tables
store_metric_events(event_id INT, date_str VARCHAR(10), store_id INT, metric VARCHAR(10), value INT)
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.