A pivot-table builder is a query compiler with a hierarchical grid interface. Field chips describe grouping and aggregate intent. The server or local engine executes that intent. Typed grouping paths keep detail rows, null members, subtotals, and grand totals unambiguous.
Dragging “Region” into Rows looks like a presentation change. It is actually a new query. Adding “Customer” can multiply the result by tens of thousands. Moving a non-additive measure can change whether a subtotal is mathematically valid. Expanding one row can request another hierarchy page.
The design needs a field catalog, canonical pivot specification, validation and cost estimation, query ownership, normalized result tree, bounded renderer, and saved-view migration. The visible matrix is a projection of those systems.
Clarify the analytical product
Ask where source data lives, whether it is already aggregated, dataset size, freshness, permissions, supported dimensions and measures, expected cardinality, subtotal rules, drill-through, exports, saved views, and accessibility requirements.
For this interview, assume an authenticated analytics product over a remote sales dataset. Users place dimensions in row and column zones, measures in Values, and predicates in Filters. The product supports subtotals, grand totals, expand and collapse, formatting, saved views, and export. The server owns authorization and aggregation. The browser owns layout intent, validation feedback, request generations, the result tree, expansion state, focus, and rendering.
Spreadsheet formulas, arbitrary SQL, chart authoring, and cube administration are separate systems.
The public interview prompt
Design a reusable pivot-table builder. Explain:
- semantic field metadata, field zones, drag behavior, validation, and cost estimation;
- the canonical query specification, request generations, caching, cancellation, and saved views;
- grouping sets, rollups, measures, nulls, subtotals, grand totals, and formatting;
- hierarchical result identity, expansion, pagination, normalization, and virtualization;
- server versus browser execution, workers, columnar data, partial and stale results;
- keyboard interaction, focus, selection, treegrid semantics, and narrow-screen alternatives;
- security, privacy thresholds, internationalization, performance, telemetry, and testing;
- export, drill-through, rollout, rejected approaches, and interviewer follow-ups.
What the premium solution covers
The full solution defines a semantic field catalog, canonical pivot specification, compatibility validator, cardinality budget, generation guard, typed grouping path, normalized result tree, expansion contract, rendering model, and saved-view migration.
Original media follows a field layout into an owned result hierarchy, shows the field-zone grammar, explains subtotal math, and separates browser orchestration from analytical execution. Two deterministic labs test layout risk and stale hierarchy responses.