Quick Overview

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.

Implement a pivot table transformation

Company: Instacart

Role: Software Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

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.

Overview: 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.

Pivot transaction amounts by category and month

You are given a transactions table with the following columns: - user_id (string) - category (string) - subcategory (string) - amount (decimal) - ts (timestamp, stored in UTC) Write a SQL query that produces a pivoted report where: - Each row represents a category. - There is one column per calendar month from January 2025 to March 2025. - The value in each cell is the sum(amount) for that (category, month) combination. - If a category has no transactions in a given month, the corresponding cell should be 0. - Ignore rows where category IS NULL. Name the monthly columns as amt_2025_01, amt_2025_02, and amt_2025_03.

Tables

transactions(user_id VARCHAR(20), category VARCHAR(50), subcategory VARCHAR(50), amount DECIMAL(10,2), ts TIMESTAMP)

Hints

  1. Use conditional aggregation with CASE expressions to turn rows into columns.
  2. Define one SUM(CASE ...) expression per month and group by category.

Pivot transaction counts by user, category, and subcategory

Using the same transactions table, write a SQL query that produces a pivoted report where: - Each row represents a (user_id, category) pair. - Each column represents a subcategory present in the data. - The value in each cell is the count(*) of transactions for that (user_id, category, subcategory) combination. - Treat NULL category values as rows to be ignored (do not include them in the output). For the given sample data, produce columns for the subcategories: Groceries, Restaurant, Flight, Hotel, Movies, Games, and Other. Name the columns groceries_cnt, restaurant_cnt, flight_cnt, hotel_cnt, movies_cnt, games_cnt, and other_cnt.

Tables

transactions(user_id VARCHAR(20), category VARCHAR(50), subcategory VARCHAR(50), amount DECIMAL(10,2), ts TIMESTAMP)

Hints

  1. Use COUNT(CASE WHEN subcategory = 'X' THEN 1 END) to compute counts per subcategory.
  2. Group by both user_id and category to get one row per pair.

Loading coding console...