Build a Pivot Table Aggregator
Company: Sigmacomputing
Role: Software Engineer
Category: Coding & Algorithms
Difficulty: medium
Interview Round: Technical Screen
Implement a function that builds a pivot table from a list of records. Each record contains a `row_key`, a `column_key`, and a numeric `value`.
Requirements:
1. In the basic version, aggregate records by `(row_key, column_key)` and compute the `sum` for each group.
2. Return the result in a structure that allows efficient lookup by row key and column key.
3. As a follow-up, explain how you would extend the implementation to support other aggregation functions such as `average` and `minimum`.
Example input:
```text
[
("A", "X", 10),
("A", "X", 20),
("A", "Y", 5),
("B", "X", 7)
]
```
Expected result for `sum`:
- `A / X -> 30`
- `A / Y -> 5`
- `B / X -> 7`
In your explanation, describe what intermediate state must be maintained so that `average` and `minimum` can also be computed correctly.
Quick Answer: This question evaluates a candidate's ability to implement data aggregation and efficient lookup structures, focusing on grouping, associative aggregations, and maintaining intermediate state for derived metrics.
Part 1: Build a Sum-Based Pivot Table Aggregator
You are given a list of records. Each record is a tuple of the form (row_key, column_key, value). Build a pivot table that groups records by the pair (row_key, column_key) and stores the sum of all values in that group.
Return the result as a nested dictionary so that lookups are efficient: result[row_key][column_key] should give the aggregated sum for that cell.
Do not create entries for missing row/column combinations. If the input is empty, return an empty dictionary.
Constraints
- 0 <= len(records) <= 200000
- row_key and column_key are strings
- value is an integer or float
- Only row/column pairs that appear in the input should appear in the output
Examples
Input: ([('A', 'X', 10), ('A', 'X', 20), ('A', 'Y', 5), ('B', 'X', 7)],)
Expected Output: {'A': {'X': 30, 'Y': 5}, 'B': {'X': 7}}
Explanation: The pair ('A', 'X') appears twice, so its values are added: 10 + 20 = 30.
Input: ([],)
Expected Output: {}
Explanation: Edge case: no records means no pivot table entries.
Hints
- A hash map (dictionary) is a natural fit for fast grouping and lookup.
- Think of the result as a dictionary of dictionaries: first by row, then by column.
Part 2: Extend the Pivot Table Aggregator to Support Sum, Average, and Minimum
You are given a list of records. Each record is a tuple of the form (row_key, column_key, value). Implement a pivot table aggregator that groups records by (row_key, column_key) and supports three aggregation modes:
- "sum": sum all values in each cell
- "average": compute the arithmetic mean of values in each cell
- "minimum": keep the smallest value in each cell
Return the result as a nested dictionary so that result[row_key][column_key] gives the aggregated value.
Important design idea: different aggregation functions require different intermediate state. For example, average cannot be computed correctly from just the latest value; it needs both a running total and a count. Minimum needs the smallest value seen so far for each cell.
If the input is empty, return an empty dictionary. You may assume agg_func is always one of "sum", "average", or "minimum".
Constraints
- 0 <= len(records) <= 200000
- row_key and column_key are strings
- value is an integer or float
- agg_func is one of: "sum", "average", "minimum"
- Only row/column pairs that appear in the input should appear in the output
Examples
Input: ([('A', 'X', 10), ('A', 'X', 20), ('A', 'Y', 5), ('B', 'X', 7)], 'average')
Expected Output: {'A': {'X': 15.0, 'Y': 5.0}, 'B': {'X': 7.0}}
Explanation: For ('A', 'X'), average = (10 + 20) / 2 = 15.0.
Input: ([('A', 'X', 10), ('A', 'X', 20), ('A', 'Y', 5), ('B', 'X', 7)], 'minimum')
Expected Output: {'A': {'X': 10, 'Y': 5}, 'B': {'X': 7}}
Explanation: For each cell, keep the smallest value seen.
Hints
- For average, each cell needs more than one number while processing. What two pieces of information are enough?
- You can maintain one nested dictionary for state while scanning the records, then convert that state into the final pivot table.