5.1 Advanced Aggregations, Window Functions & Analytics

Databricks SQL provides an enterprise-grade SQL authoring engine powered by the Photon query engine. For data analysts working with large-scale analytical workloads on the Databricks Data Intelligence Platform, mastering advanced aggregation techniques, window functions, and statistical approximation functions is essential. These capabilities enable deep data analysis, complex metrics calculation, and multi-dimensional reporting without requiring data to be exported to external BI tools or processed using complex Python scripts.

Multi-Dimensional Aggregations: GROUPING SETS, ROLLUP, and CUBE

Standard GROUP BY clauses aggregate data along a single set of dimensions. However, business reporting frequently requires aggregate metrics across multiple dimensional hierarchies—such as sales by region, sales by product category, and overall total sales. Traditionally, analysts achieved this by executing multiple queries joined with UNION ALL, which scans the underlying table repeatedly. Databricks SQL provides advanced grouping operators that evaluate multiple aggregation levels in a single optimized table scan.

1. GROUP BY GROUPING SETS

GROUPING SETS allows you to explicitly define the exact combinations of dimensions you wish to aggregate.

SELECT 
    region, 
    product_category, 
    SUM(revenue) AS total_revenue,
    GROUPING_ID(region, product_category) AS grouping_level
FROM main.sales.orders
GROUP BY GROUPING SETS (
    (region, product_category),
    (region),
    ()
);

In this query:

• (region, product_category) computes revenue for each category within each region.
• (region) computes total revenue per region across all categories.
• () computes the grand total revenue across all regions and categories.
• GROUPING_ID() returns a bitmask integer identifying which grouping set produced each row, enabling easy filtering in downstream visual layers.

2. GROUP BY ROLLUP

ROLLUP creates a hierarchical sequence of grouping sets, aggregating from the most detailed level up to the grand total. It is ideal for hierarchical data such as (year, quarter, month) or (country, state, city).

-- Aggregates (country, state), (country), and ()
SELECT country, state, SUM(sales_amount) AS sales
FROM main.sales.store_sales
GROUP BY ROLLUP (country, state);

3. GROUP BY CUBE

CUBE generates grouping sets for all possible combinations of the specified dimensions. For $N$ dimensions, CUBE generates $2^N$ grouping combinations.

---

Window Functions Mechanics & Framing Rules

Window functions perform calculations across a set of table rows related to the current row, preserving individual row identity without collapsing the result set. The syntax requires an OVER clause specifying partitioning, ordering, and frame boundaries:

$\text{FUNCTION}()\ \text{OVER}\ (\text{PARTITION BY}\ \text{col1}\ \text{ORDER BY}\ \text{col2}\ [\text{frame\_clause}])$

Window Framing: ROWS vs. RANGE

The window frame specification defines the exact boundaries of rows included in the window relative to the current row. Databricks SQL supports two framing modes:

1. ROWS Frame: Defines boundaries based on physical row counts relative to the current row position.
2. RANGE Frame: Defines boundaries based on logical value differences relative to the value in the ORDER BY column.

SELECT 
    order_date,
    daily_amount,
    -- Physical 7-day moving average (current row plus previous 6 rows)
    AVG(daily_amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d,
    -- Default RANGE frame when ORDER BY is present without explicit framing
    SUM(daily_amount) OVER (
        ORDER BY order_date 
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_sum
FROM main.finance.daily_revenue;

Exam Tip & Critical Trap: If an ORDER BY clause is provided inside OVER() without an explicit frame clause, Databricks SQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. If duplicate values exist in the ORDER BY column, RANGE includes all peer rows with identical values in the aggregate, whereas ROWS evaluates strictly up to the physical row position.

---

Ranking Functions: ROW_NUMBER, RANK, and DENSE_RANK

Databricks SQL provides three core functions for ordering and ranking rows within a partition. Understanding how each handles tied values is heavily tested on the Data Analyst Associate exam.

SELECT 
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM main.hr.employees;

Comparison of Ranking Functions on Tied Values

Suppose three employees in the Sales department earn an identical salary of $100,000, following an employee earning $120,000:

• ROW_NUMBER(): Assigns a unique, non-deterministic sequential integer to every row regardless of ties.
• RANK(): Assigns identical ranks to tied rows, but creates gaps in the sequence equal to the number of tied rows.
• DENSE_RANK(): Assigns identical ranks to tied rows, but maintains a contiguous integer sequence without gaps.

---

Inter-Row Analytics: LAG, LEAD, and Statistical Approximations

Offset Functions: LAG and LEAD

LAG() and LEAD() access values from previous or subsequent rows without executing expensive self-joins.

SELECT 
    customer_id,
    order_timestamp,
    amount,
    LAG(amount, 1, 0.0) OVER (PARTITION BY customer_id ORDER BY order_timestamp) AS prev_order_amount,
    order_timestamp - LAG(order_timestamp) OVER (PARTITION BY customer_id ORDER BY order_timestamp) AS time_since_last_order
FROM main.sales.orders;

The third argument in LAG(column, offset, default_value) specifies a fallback value returned when the offset points before the beginning of the partition, eliminating unintended NULL values in downstream computations.

Approximate Aggregations for Big Data Scale

When querying petabyte-scale datasets on Databricks SQL Warehouses, exact distinct counting (COUNT(DISTINCT col)) requires shuffling all distinct key values across cluster nodes, leading to heavy network I/O and high memory overhead. Databricks SQL offers HyperLogLog-based approximation functions:

• approx_count_distinct(col [, relativeSD]): Computes an approximate distinct count with a default relative standard deviation of 0.05 (5%). Setting relativeSD = 0.01 increases precision while using a fraction of the memory of COUNT(DISTINCT).
• approx_percentile(col, percentage [, accuracy]): Calculates approximate percentiles (e.g., approx_percentile(latency, 0.95)) for latency SLA and distribution analysis.