ExamFinch.com My Study

Study guide

5.3 Window Functions and Complex Joins in Spark SQL

Practice this exam

Databricks Certified Associate Developer for Apache Spark — Free Study Guide 2026 Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Spark Architecture and Execution

1.1 Spark Benefits, Modules, and DataFrame/Dataset Concepts 1.2 Driver, Executors, and Cluster Managers 1.3 Execution Hierarchy: Applications, Jobs, Stages, and Tasks 1.4 Lazy Evaluation, Transformations vs Actions, and Resilient Lineage

2 Chapter 2: Shuffles, Memory and Persistence

2.1 Narrow vs Wide Dependencies and the Shuffle Mechanism 2.2 Memory Management, Fault Tolerance, and Garbage Collection 2.3 Caching, Persistence, and StorageLevels

3 Chapter 3: Spark SQL Sessions, Tables and Views

3.1 SparkSession, Catalogs, Databases, and Namespaces 3.2 Managed vs External Tables and CTAS 3.3 Views: Temporary vs Global Temporary Views

4 Chapter 4: SQL Sources and Stored Layout

4.1 JDBC Reads, Writes, and Parallelism 4.2 Direct File SQL, Save Modes, and Persistent Layout

5 Chapter 5: SQL Queries and Analytics

5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions 5.2 Built-in SQL Expressions and an Integrated Query 5.3 Window Functions and Complex Joins in Spark SQL 5.4 SQL Join Semantics and Integrated Analytics

6 Chapter 6: DataFrame Construction and Columns

6.1 DataFrame Construction, Schemas, and StructTypes 6.2 Column Operations: Selecting, Renaming, Deriving, and Casting 6.3 Complex Nested Types and User-Defined Functions

7 Chapter 7: DataFrame Validation and Inspection

7.1 Row Operations: Filtering, Sorting, and Deduplication 7.2 Handling Missing and Null Data 7.3 Schema Inspection, Row Iteration, and List Conversion

8 Chapter 8: Aggregations and Dates

8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting 8.2 Dates, Unix Epoch, and Time-Zone Conversions

9 Chapter 9: DataFrame Joins and File IO

9.1 DataFrame Joins and Combining Datasets 9.2 Data Sources, SaveModes, and Partitioning

10 Chapter 10: Shared Variables and Stateful Processing

10.1 Broadcast Variables, Accumulators, and Closures 10.2 Stateless UDFs, Stateful Operators, and StateStores

11 Chapter 11: Troubleshooting and Tuning

11.1 Diagnosing Performance with the Spark UI & Execution Plans 11.2 Data Skew and Partition Tuning: repartition vs coalesce 11.3 Join Optimization and Broadcast Hash Joins

12 Chapter 12: Structured Streaming

12.1 Streaming Architecture, Sources, and Sinks 12.2 Output Modes, Triggers, and Checkpointing 12.3 Event-Time Processing, Watermarking, and Windowing 12.4 Streaming Deduplication With and Without Watermarks

13 Chapter 13: Spark Connect and Deployment

13.1 Spark Connect Architecture & Client-Server Decoupling 13.2 Working with Spark Connect: Connections and Best Practices

14 Chapter 14: Pandas API on Spark

14.1 Pandas API on Apache Spark: Core Architecture and Syntax 14.2 Pandas-on-Spark Operations, Indexing, and Optimization

5.3 Window Functions and Complex Joins in Spark SQL

Key Takeaways Explain This Topic AI

Window functions preserve input row detail while evaluating a frame, rank or offset.

ROW_NUMBER breaks ties arbitrarily unless ORDER BY includes a complete tie breaker; RANK leaves rank gaps and DENSE_RANK does not.

For aggregate/value windows, an ordered default frame is cumulative RANGE including peers; LAST_VALUE does not necessarily mean the final partition value.

ROWS describes row offsets, while RANGE describes ordering-value offsets and peers.

Last updated: October 2026

In standard relational queries, aggregation operations grouped by keys (using GROUP BY ) collapse all contributing rows into a single summary record per group. While suitable for high-level metric rollups, grouping destroys row-level detail. Window functions overcome this limitation by computing aggregations, moving averages, relative ranks, or offsets over a designated group of rows (called a window frame ) while preserving the unique identity of each individual row in the output.

Alongside window functions, advanced data pipelines rely on complex join strategies—such as Semi Joins, Anti Joins, and Cross Joins—to filter, reconcile, and combine distributed datasets efficiently across cluster nodes.

1. Window Specification Architecture: Syntax and Execution Mechanics

A window function calculates a return value for every row in a relation based on a defined subset of rows related to the current row. In Spark SQL, window operations are declared using the OVER clause.

  • The Anatomy of the OVER Clause
  • A window specification comprises three distinct clauses: function_name(expression) OVER (
  • [PARTITION BY partition_col1, partition_col2, ...]
  • [ORDER BY sort_col1 [ASC|DESC] [NULLS FIRST|LAST], ...]
  • [ROWS|RANGE BETWEEN frame_start AND frame_end]
  • )

PARTITION BY : Divides input rows into logical partitions across cluster nodes. The window function evaluates independently within each partition. If omitted, the entire dataset is treated as a single partition (which can cause severe executor memory bottlenecks).

ORDER BY : Determines the physical ordering of rows within each partition. Mandatory for ranking and offset functions, and establishes cumulative ordering for aggregate functions.

Frame Specification ( ROWS / RANGE ) : Defines the subset of rows within the partition that bounds the calculation relative to the current row.

Reusing Window Definitions: The WINDOW Clause

When multiple analytical functions share the same partitioning and ordering rules, writing duplicate OVER (...) clauses creates verbose, error-prone code. Spark SQL supports the ANSI SQL WINDOW clause to define reusable window aliases at the end of the query: SELECT

  • department_id,
  • employee_id,
  • salary,
  • AVG(salary) OVER w AS dept_avg_salary,
  • SUM(salary) OVER w AS dept_running_total,
  • MAX(salary) OVER w AS dept_max_salary
  • FROM corporate.employees
  • WINDOW w AS (
  • PARTITION BY department_id
  • ORDER BY salary DESC
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • );
  • Programmatic PySpark Window API

In PySpark, window operations are formulated using pyspark.sql.window.Window : from pyspark.sql.window import Window

import pyspark.sql.functions as F

  • # Define the window specification
  • window_spec = (
  • Window.partitionBy("department_id")
  • .orderBy(F.col("salary").desc())
  • .rowsBetween(Window.unboundedPreceding, Window.currentRow)
  • )
  • # Apply the window specification
  • df_ranked = df.withColumn("dept_running_total", F.sum("salary").over(window_spec))
  • Physical Execution Mechanics: Shuffling and Sorting

A window physical plan can include distribution, sorting and a window operator; these are operators, not necessarily three separate scheduler stages:

Exchange (Shuffle) : If PARTITION BY is specified, Spark applies hash-partitioning on the partition columns to ensure all rows sharing the same partition key land on the same executor JVM.

In-Memory Sort ( SortExec ) : Within each partition, rows are sorted by the ORDER BY columns.

Window Operator ( WindowExec ) : A specialized physical operator sweeps through the sorted partition, maintaining a sliding frame buffer to evaluate aggregates or rank calculations.

2. Ranking Functions: ROW_NUMBER , RANK , DENSE_RANK , and NTILE

Ranking functions assign an ordinal integer to each row within a partition based on the ORDER BY specification. How these functions handle duplicate (tied) values is one of the most frequently tested concepts on the Databricks certification exam.

Comparative Ranking Mechanics

ROW_NUMBER() : Generates a strict, unique sequential integer (1, 2, 3, ...) for every row in the partition. If two rows have identical sort values, ROW_NUMBER() assigns distinct numbers non-deterministically unless additional tie-breaking columns are added to ORDER BY .

RANK() : Assigns identical rank numbers to rows with identical sort values. However, it leaves gaps in the sequence equal to the number of tied rows. The next non-tied row receives a rank equal to: 1 + count of all preceding rows 1 + \text{count of all preceding rows} 1 + count of all preceding rows .

DENSE_RANK() : Assigns identical rank numbers to rows with identical sort values, but never leaves gaps . The next non-tied row receives the immediate next sequential integer.

NTILE(n) : Divides rows within each partition as equally as possible into n n n numbered buckets (quantiles), ranging from 1 to n n n . If the row count is not evenly divisible by n n n , earlier buckets receive one extra row.

PERCENT_RANK() : Computes the relative rank of a row as a percentage from 0.0 to 1.0, calculated as: RANK − 1 Total Rows in Partition − 1 \frac{\text{RANK} - 1}{\text{Total Rows in Partition} - 1} Total Rows in Partition − 1 RANK − 1 ​ .

CUME_DIST() : Calculates the cumulative distribution of a value, defined as: Number of rows preceding or tied with the current row in sort order Total Rows in Partition \frac{\text{Number of rows preceding or tied with the current row in sort order}}{\text{Total Rows in Partition}} Total Rows in Partition Number of rows preceding or tied with the current row in sort order ​ .

Comparative Ranking Table on Duplicate Values

Consider a department containing eight employees with the following salaries: [100k, 90k, 90k, 80k, 70k, 70k, 70k, 50k] :

  • Employee ID Salary ROW_NUMBER() RANK() DENSE_RANK() NTILE(3) PERCENT_RANK() CUME_DIST()
  • E1 $100k 1 1 1 1 0.00 0.125 (1/8)
  • E2 $90k 2 2 2 1 0.14 (1/7) 0.375 (3/8)
  • E3 $90k 3 2 2 1 0.14 (1/7) 0.375 (3/8)
  • E4 $80k 4 4 3 2 0.43 (3/7) 0.500 (4/8)
  • E5 $70k 5 5 4 2 0.57 (4/7) 0.875 (7/8)
  • E6 $70k 6 5 4 2 0.57 (4/7) 0.875 (7/8)
  • E7 $70k 7 5 4 3 0.57 (4/7) 0.875 (7/8)
  • E8 $50k 8 8 5 3 1.00 (7/7) 1.000 (8/8)

Notice that for RANK() , when E2 and E3 tie at rank 2, E4 jumps immediately to rank 4 (skipping 3). For DENSE_RANK() , E4 receives rank 3 without any gap.

3. Analytic and Offset Functions

Offset functions allow queries to inspect values from adjacent rows without performing self-joins.

Offset Functions: LAG and LEAD

LAG(col, [offset=1], [default=null]) : Returns the value of col from N N N rows before the current row within the partition.

LEAD(col, [offset=1], [default=null]) : Returns the value of col from N N N rows after the current row within the partition. SELECT

  • customer_id,
  • order_date,
  • order_amount,
  • - Amount spent on the immediate previous order
  • LAG(order_amount, 1, 0.0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount,
  • - Days elapsed since the previous order

datediff(order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)) AS days_since_prev_order

  • FROM sales.orders;
  • Value Functions: FIRST_VALUE and LAST_VALUE

FIRST_VALUE(col [, ignoreNulls]) : Returns the value of col from the first row of the window frame.

LAST_VALUE(col [, ignoreNulls]) : Returns the value of col from the last row of the window frame.

The Classic LAST_VALUE() Exam Trap

A critical trap occurs when developers invoke LAST_VALUE() using the default window frame. By default, when ORDER BY is present, the frame is: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW \texttt{RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW} RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Under this cumulative frame, the "last row" evaluated by the function is the last peer with the current ordering value , which is the current row when ordering values are unique. Consequently, LAST_VALUE() is evaluated over the current cumulative frame as the query progresses down the partition, rather than the final value of the entire partition. -- BUG: Returns the current row's order_amount, NOT the final order amount!

  • SELECT customer_id, order_date, order_amount,
  • LAST_VALUE(order_amount) OVER (PARTITION BY customer_id ORDER BY order_date, order_id) AS wrong_last
  • FROM sales.orders;
  • - CORRECT: Explicitly extend the frame to the end of the partition
  • SELECT customer_id, order_date, order_amount,
  • LAST_VALUE(order_amount) OVER (
  • PARTITION BY customer_id
  • ORDER BY order_date, order_id
  • ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  • ) AS true_last
  • FROM sales.orders;
  • 4. Window Frame Specifications: ROWS vs RANGE

The frame clause defines the physical or logical slice of rows within a partition evaluated by the window function.

Frame Boundary Syntax [ROWS | RANGE] BETWEEN frame_start AND frame_end

Valid boundary keywords include:

UNBOUNDED PRECEDING : Frame begins at the very first row of the partition.

n PRECEDING : Frame starts n n n physical rows before the current row ( ROWS ) or n n n value units before ( RANGE ).

CURRENT ROW : The active row being evaluated.

n FOLLOWING : Frame extends n n n physical rows ahead ( ROWS ) or n n n value units ahead ( RANGE ).

UNBOUNDED FOLLOWING : Frame extends to the very last row of the partition.

ROWS vs RANGE : The Fundamental Difference

ROWS (Physical Offsets) : Operates purely on row index positions. For example, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING always evaluates exactly up to 3 rows (the previous row, the current row, and the next row), regardless of whether column values are identical.

RANGE (Logical Value Offsets) : Operates on the actual values of the ORDER BY expression. In Spark SQL:

Tied Sort Values : If multiple rows share the exact same value in the ORDER BY column, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW considers all tied rows as peers and evaluates the aggregate across all of them together up to the end of the tied group.

Numeric and Date Intervals : RANGE can evaluate logical intervals, such as RANGE BETWEEN INTERVAL 7 DAYS PRECEDING AND CURRENT ROW , aggregating all transactions that occurred in the 7 days prior to the current transaction date regardless of how many rows exist.

Default Window Frame Rules

The following defaults apply to ordinary aggregate/value windows. Ranking and offset functions can require their own frames or reject explicit custom frames; do not impose this table on every function.

  • Specification Default Window Frame
  • Window contains ORDER BY (no explicit frame) RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • Window contains NO ORDER BY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING