5.1 Advanced Aggregations, Window Functions & Analytics

Databricks Certified Data Analyst Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Platform Overview & Unity Catalog Foundations

1.1 Databricks Architecture & Compute Concepts 1.2 Databricks Marketplace & Data Sharing Basics 1.3 Unity Catalog Namespace & Object Hierarchy 1.4 Governed Asset Discovery & System Tables

2 Managing & Cleaning Governed Data

2.1 Dataset Discovery & Asset Tagging 2.2 Lineage Inspection & Data Traceability 2.3 SQL Data Cleaning & Transformation Techniques

3 Data Ingestion & Import Patterns

3.1 Workspace Uploads & Cloud File Ingestion 3.2 Incremental File Ingestion with Auto Loader 3.3 Ingesting Data via Delta Sharing & External Connections

4 Databricks SQL Warehouses & Core Querying

4.1 Databricks SQL Interface & SQL Assistant 4.2 SQL Warehouses Sizing & Management 4.3 Table Creation & Data Modification in Databricks SQL

5 Advanced SQL Authoring & Time Travel

5.1 Advanced Aggregations, Window Functions & Analytics 5.2 Complex Joins, CTEs & Set Operations 5.3 Views, Materialized Views & Delta Time Travel

6 Query Performance Analysis & Photon Engine

6.1 Photon Engine Architecture & Acceleration 6.2 Query History & Execution Profile Inspection 6.3 Delta Lake Audit History & Commit Logs

7 Liquid Clustering, Caching & Performance Troubleshooting

7.1 Caching Hierarchy in Databricks SQL 7.2 Data Layout Optimization: Liquid Clustering vs Partitioning & Z-Ordering 7.3 Troubleshooting Slow & Failing Queries

8 AI/BI Dashboards & Visualization Design

8.1 AI/BI Dashboards Overview & Canvas Layout 8.2 Chart Types & Visualization Selection 8.3 Notebook & SQL Editor Visualizations

9 Dashboard Interactivity, Publishing & Alerts

9.1 Dashboard Parameters & Interactive Filtering 9.2 Publishing, Sharing & Access Control 9.3 Scheduled Refresh, Email Subscriptions & Alerts

10 Developing & Maintaining AI/BI Genie Spaces

10.1 AI/BI Genie Architecture & Space Setup 10.2 Curation via Instructions, Descriptions & Sample Questions 10.3 Trusted Assets & Parameterized Functions 10.4 Monitoring, Benchmarks, Feedback & Maintenance

11 Data Modeling & Medallion Architecture

11.1 Dimensional Modeling Fundamentals 11.2 Medallion Architecture for Analytics 11.3 Constraint Enforcement & Relationships in Unity Catalog

12 Securing Data & Unity Catalog Governance

12.1 Unity Catalog Privilege Model & Grants 12.2 Ownership, Inheritance & Access Control Patterns 12.3 Fine-Grained Security: Row Filters & Column Masks

5.1 Advanced Aggregations, Window Functions & Analytics

Key Takeaways Explain This Topic AI

Databricks SQL supports GROUP BY GROUPING SETS, CUBE, and ROLLUP to generate multi-level summary rows in a single pass, reducing execution scan overhead by up to 3x compared to multiple UNION ALL queries.

Window partitioning functions like ROW_NUMBER(), RANK(), and DENSE_RANK() assign sequence numbers over partitioned windows; DENSE_RANK() leaves no gaps in ranking sequences (e.g., 1, 2, 2, 3) whereas RANK() produces gaps (e.g., 1, 2, 2, 4).

The default window frame specification when an ORDER BY clause is present without an explicit frame boundary is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which aggregates ties together rather than processing row by row like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Analytic functions like LAG(col, offset, default) and LEAD(col, offset, default) enable inter-row comparison without self-joins, executing in O(N log N) time over partitioned datasets.

High-cardinality statistical aggregations in Databricks SQL utilize HyperLogLog algorithms via approx_count_distinct() to deliver results within a standard error of ~1.04/sqrt(m) using less than 1% of the memory required by an exact COUNT(DISTINCT).

Last updated: July 2026

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 N N dimensions, CUBE generates 2 N 2^N 2 N grouping combinations.

• Aggregation Clause Generated Grouping Sets Primary Use Case
• GROUP BY A, B (A, B) Single-level detailed aggregation
• GROUP BY ROLLUP (A, B) (A, B) , (A) , () Hierarchical multi-level subtotals
• GROUP BY CUBE (A, B) (A, B) , (A) , (B) , () Full cross-dimensional matrix analysis
• GROUP BY GROUPING SETS ((A,B), (A)) (A, B) , (A) Custom selective subtotal 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: FUNCTION ( ) OVER ( PARTITION BY col1 ORDER BY col2 [ frame_clause ] ) \text{FUNCTION}()\ \text{OVER}\ (\text{PARTITION BY}\ \text{col1}\ \text{ORDER BY}\ \text{col2}\ [\text{frame\_clause}]) FUNCTION ( ) OVER ( PARTITION BY col1 ORDER BY col2 [ 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:

ROWS Frame : Defines boundaries based on physical row counts relative to the current row position.

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:

• Employee Department Salary ROW_NUMBER() RANK() DENSE_RANK() Explanation of Behavior
• Alice Sales $120,000 1 1 1 Highest salary in partition
• Bob Sales $100,000 2 2 2 Tie for 2nd place
• Charlie Sales $100,000 3 2 2 Tie for 2nd place
• David Sales $100,000 4 2 2 Tie for 2nd place
• Eva Sales $90,000 5 5 3 RANK() skips to 5; DENSE_RANK() advances to 3

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.