Implement a pivot table transformation

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Instacart.

Q: What role is this question designed for?

This question is commonly asked for Software Engineer candidates at Instacart during technical interviews.

Question

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.

PracHub · Accepted Answer

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.

Quick Overview

Implement a pivot table transformation