SQL SQL In Depth
0%
Guided SQL & Engine Coding Lab

Multi-Dimensional Revenue, Cohort & Funnel Aggregation Suite

A single-pass multi-dimensional OLAP aggregation engine implementing conditional FILTER (WHERE ...), GROUPING SETS / ROLLUP subtotals with bitmask GROUPING() flags, and exact/interpolated percentiles.

~35 min Intermediate Lessons: Hands-On Build: Multi-Dimensional Revenue, Cohort & Funnel Aggregation Suite, Aggregate Internals: COUNT(*) vs COUNT(col), COUNT(DISTINCT), SUM & AVG, GROUP BY Execution Mechanics, HashAggregate vs GroupAggregate & Functional Dependency, Pre-Filter (WHERE) vs Post-Aggregate Filter (HAVING) Pipeline, Pivoting & Conditional Aggregation: SUM(CASE WHEN) & FILTER (WHERE ...), List & Document Rollups: GROUP_CONCAT, STRING_AGG & JSON Aggregation
Guided Algorithm Coding Lab 0/4 Steps Completed

Code & Verify SQL & Storage Engine Invariants in the Browser Workbench

Complete 4 progressive algorithmic steps with guided hints in C++20 (WebAssembly), TypeScript, or JavaScript against automated unit test assertions — zero local setup required.

Architectural Overview

A single-pass multi-dimensional OLAP aggregation engine implementing conditional FILTER (WHERE ...), GROUPING SETS / ROLLUP subtotals with bitmask GROUPING() flags, and exact/interpolated percentiles.

In production relational and analytical database systems—spanning PostgreSQL, SQLite, MySQL/InnoDB, and DuckDB—understanding how query clauses, schema constraints, index structures, and storage engines execute under the hood separates surface-level query authors from staff-level database engineers. This capstone build lab guides you through implementing Multi-Dimensional Revenue, Cohort & Funnel Aggregation Suite from first principles with deterministic self-test invariants.

Guided Implementation Walkthrough (4 Progressive Steps)

Step 1: Single-Pass Conditional Aggregation (FILTER Clause)

  • Core Database Concept: Pivoting Funnel & Revenue Metrics in One Table Scan
  • Target Module: index.js (OlapAggregationSuite.conditionalAggregate)
  • Engineering Specification: Implement conditionalAggregate(orders, groupCol) to compute total_orders, paid_orders, paid_revenue_cents, and refunded_revenue_cents per group in a single table scan without self-joins.
  • Implementation Hint: Accumulate totalOrders, paidOrders, paidRevenue, and refundedRevenue in a single loop per group key.
  • Verification Invariant: deterministic assertion verified in test.js step 1

Step 2: Hierarchical ROLLUP & Bitmask GROUPING() Indicator

  • Core Database Concept: Generating Prefix Subtotals and Distinguishing Super-Aggregate NULLs
  • Target Module: index.js (OlapAggregationSuite.rollupRevenue)
  • Engineering Specification: Implement rollupRevenue(orders, dim1, dim2) to compute hierarchical subtotals across (dim1, dim2), (dim1, null), and the grand total (null, null), setting grouping_id (0, 1, or 3) to distinguish real NULL keys from rollup super-aggregates.
  • Implementation Hint: Emit groupings for (region, plan), (region, null) with groupingMask=1, and (null, null) with groupingMask=3.
  • Verification Invariant: deterministic assertion verified in test.js step 2

Step 3: Continuous Linear-Interpolated Percentile (PERCENTILE_CONT)

  • Core Database Concept: Order-Set Aggregate Interpolation Between Adjacent Ranks
  • Target Module: index.js (OlapAggregationSuite.percentileCont)
  • Engineering Specification: Implement percentileCont(values, p) to ignore null/undefined inputs, sort the remaining numeric values, and linearly interpolate at fractional index pos = p * (n - 1).
  • Implementation Hint: Sort non-null values ascending; compute pos = p * (n - 1), lo = Math.floor(pos), hi = Math.ceil(pos).
  • Verification Invariant: deterministic assertion verified in test.js step 3

Step 4: HAVING Post-Aggregation Group Filter

  • Core Database Concept: Filtering Aggregated Buckets After Group Collapse
  • Target Module: index.js (OlapAggregationSuite.filterHaving)
  • Engineering Specification: Implement filterHaving(groups, minPaidRevenueCents, minConversionRate) to retain only groups meeting post-aggregation business thresholds.
  • Implementation Hint: Filter the aggregated summary array using havingPredicate(groupSummary).
  • Verification Invariant: deterministic assertion verified in test.js step 4

Relational & Storage Engine Guarantees

  • Curriculum Domain: Aggregation & Grouping Sets
  • Difficulty & Time Budget: Intermediate (~35 minutes)
  • Key Architectural Highlights:
    • Single-pass conditional aggregation equivalent to COUNT/SUM(...) FILTER (WHERE ...)
    • Hierarchical ROLLUP and GROUPING SETS generator with bitmask GROUPING(col) metadata
    • Exact discrete (PERCENTILE_DISC) and continuous interpolated (PERCENTILE_CONT) quantiles
  • Core Primitives & Tags: GROUPING SETS, ROLLUP, Conditional Aggregation, FILTER Clause, Percentiles

Related Curriculum Units

Production Database Context & Verification

This from-scratch relational engine component is engineered with zero external dependencies and deterministic verification harnesses. Every internal database invariant—from three-valued logic (TRUE / FALSE / UNKNOWN), referential integrity, and fan-out-free join pre-aggregation to B+Tree page splits, MVCC snapshot visibility (xmin / xmax), slotted-page compaction, and ARIES write-ahead log crash recovery—is asserted directly in test.js. Use the interactive browser workbench above to complete the 4 guided steps in Guided Lab mode or inspect and benchmark the complete reference architecture in Full Solution mode.