Day 14 of 32 Days of SQL Concepts — PERCENT_RANK()

PERCENT_RANK() represents a sophisticated analytical function occupying a distinct position within the window function taxonomy. Where CUME_DIST() calculates cumulative distribution by counting rows, PERCENT_RANK() computes relative positioning using rank-based mathematics. For a data engineer with your experience, PERCENT_RANK() addresses percentile ranking requirements that demand greater granularity than CUME_DIST() provides, particularly when analyzing competitive positioning, performance tiers, and relative standing within populations.

The function calculates: (rank of current row — 1) divided by (total rows in partition — 1), returning values between 0 and 1 inclusive. This mathematical formulation distinguishes PERCENT_RANK() fundamentally from its cousin CUME_DIST(), which calculates (count of rows <= current row) divided by (total rows).

Foundational Concept

Mathematical Definition

PERCENT_RANK() = (RANK() - 1) / (Total Rows in Partition - 1)

This formula yields several critical characteristics:

1. The first row always receives 0.0, regardless of value
2. The final row always receives 1.0
3. Identical values receive identical percent rank values
4. Non-identical values create gaps between consecutive percent ranks
5. The range spans [0, 1] inclusive, with both boundaries achievable

Comparative Formula Structure

To comprehensively understand PERCENT_RANK(), contrast it against related functions:

RANK()           = Ordinal position accounting for ties
PERCENT_RANK()   = (RANK - 1) / (Total Rows - 1)
CUME_DIST()      = (Row Count <= Current) / (Total Rows)
NTILE(n)         = Equal-width bucket assignment
ROW_NUMBER()     = Sequential position without gap accounting

Practical Implication

Consider a department with 10 employees ordered by salary:

Employee  Salary  RANK  PERCENT_RANK   CUME_DIST   Interpretation
1         40K     1     0.0            0.1         0th percentile (minimum)
2         45K     2     0.111          0.2         11th percentile
3         45K     2     0.111          0.2         (tied, same rank and percent)
4         50K     4     0.333          0.4         33rd percentile
5         55K     5     0.444          0.5         44th percentile
6         60K     6     0.556          0.6         56th percentile
7         65K     7     0.667          0.7         67th percentile
8         70K     8     0.778          0.8         78th percentile
9         75K     9     0.889          0.9         89th percentile
10        80K     10    1.0            1.0         100th percentile (maximum)

Notice that PERCENT_RANK() creates gaps when ties exist (employees 2 and 3 both rank 2nd), whereas CUME_DIST() calculates based on row count regardless of ranking methodology.

SQL Implementation Patterns

Pattern 1: Basic Performance Percentile Ranking

The most straightforward application ranks individual performance within populations:

SELECT 
    employee_id,
    employee_name,
    department_id,
    annual_performance_score,
    PERCENT_RANK() OVER (
        PARTITION BY department_id 
        ORDER BY annual_performance_score
    ) AS department_performance_percentile,
    ROUND(100 * PERCENT_RANK() OVER (
        PARTITION BY department_id 
        ORDER BY annual_performance_score
    ), 2) AS department_percentile_value,
    PERCENT_RANK() OVER (
        ORDER BY annual_performance_score
    ) AS company_wide_percentile,
    CASE 
        WHEN PERCENT_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY annual_performance_score
        ) >= 0.75 THEN 'Top Quartile'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY annual_performance_score
        ) >= 0.50 THEN 'Upper Middle'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY annual_performance_score
        ) >= 0.25 THEN 'Lower Middle'
        ELSE 'Bottom Quartile'
    END AS performance_tier
FROM employees
WHERE employment_status = 'Active'
ORDER BY department_id, annual_performance_score;

This query simultaneously ranks employees within their departments and across the entire organization, providing contextual performance positioning.

Pattern 2: Sales Ranking with Competitive Analysis

Analyzing where each sales representative stands among their peers:

SELECT 
    quarter,
    region,
    sales_rep_id,
    sales_rep_name,
    quarterly_revenue,
    RANK() OVER (
        PARTITION BY quarter, region 
        ORDER BY quarterly_revenue DESC
    ) AS regional_rank,
    PERCENT_RANK() OVER (
        PARTITION BY quarter, region 
        ORDER BY quarterly_revenue DESC
    ) AS regional_percentile,
    ROUND(100 * PERCENT_RANK() OVER (
        PARTITION BY quarter, region 
        ORDER BY quarterly_revenue DESC
    ), 2) AS regional_percentile_value,
    PERCENT_RANK() OVER (
        PARTITION BY quarter 
        ORDER BY quarterly_revenue DESC
    ) AS company_percentile,
    COUNT(*) OVER (
        PARTITION BY quarter, region
    ) AS reps_in_region,
    CASE 
        WHEN PERCENT_RANK() OVER (
            PARTITION BY quarter, region 
            ORDER BY quarterly_revenue DESC
        ) = 0.0 THEN 'Top Performer'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY quarter, region 
            ORDER BY quarterly_revenue DESC
        ) < 0.25 THEN 'High Performer'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY quarter, region 
            ORDER BY quarterly_revenue DESC
        ) < 0.75 THEN 'Standard Performer'
        ELSE 'Needs Improvement'
    END AS performance_category
FROM sales_performance
WHERE quarter >= 'Q1-2024'
ORDER BY quarter, region, quarterly_revenue DESC;

Pattern 3: Product Market Positioning

Determining where each product ranks within market categories:

SELECT 
    product_id,
    product_name,
    category_id,
    category_name,
    market_share_percentage,
    PERCENT_RANK() OVER (
        PARTITION BY category_id 
        ORDER BY market_share_percentage
    ) AS category_market_percentile,
    PERCENT_RANK() OVER (
        PARTITION BY category_id 
        ORDER BY market_share_percentage
    ) * 100 AS category_percentile_value,
    PERCENT_RANK() OVER (
        ORDER BY market_share_percentage
    ) * 100 AS overall_market_percentile,
    CASE 
        WHEN PERCENT_RANK() OVER (
            PARTITION BY category_id 
            ORDER BY market_share_percentage
        ) >= 0.667 THEN 'Category Leader'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY category_id 
            ORDER BY market_share_percentage
        ) >= 0.333 THEN 'Strong Contender'
        ELSE 'Developing Product'
    END AS market_position
FROM product_market_analysis
WHERE analysis_date >= DATEADD(MONTH, -12, GETDATE())
ORDER BY category_id, market_share_percentage DESC;

Pattern 4: Student Academic Ranking

Academic institutions frequently utilize PERCENT_RANK() for standardized reporting:

SELECT 
    academic_year,
    student_id,
    student_name,
    major_id,
    cumulative_gpa,
    RANK() OVER (
        PARTITION BY academic_year, major_id 
        ORDER BY cumulative_gpa DESC
    ) AS major_rank,
    PERCENT_RANK() OVER (
        PARTITION BY academic_year, major_id 
        ORDER BY cumulative_gpa DESC
    ) AS major_percentile_rank,
    PERCENT_RANK() OVER (
        PARTITION BY academic_year 
        ORDER BY cumulative_gpa DESC
    ) AS university_percentile_rank,
    ROUND(100 * PERCENT_RANK() OVER (
        PARTITION BY academic_year, major_id 
        ORDER BY cumulative_gpa DESC
    ), 2) AS major_percentile_score,
    CASE 
        WHEN PERCENT_RANK() OVER (
            PARTITION BY academic_year, major_id 
            ORDER BY cumulative_gpa DESC
        ) <= 0.05 THEN 'Summa Cum Laude'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY academic_year, major_id 
            ORDER BY cumulative_gpa DESC
        ) <= 0.15 THEN 'Magna Cum Laude'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY academic_year, major_id 
            ORDER BY cumulative_gpa DESC
        ) <= 0.30 THEN 'Cum Laude'
        ELSE 'Standard Honors'
    END AS honors_classification
FROM student_academic_records
WHERE enrollment_status = 'Active'
ORDER BY academic_year, major_id, cumulative_gpa DESC;

Pattern 5: Healthcare Patient Outcomes Ranking

Medical institutions utilize PERCENT_RANK() for comparative outcome analysis:

SELECT 
    hospital_id,
    hospital_name,
    procedure_type,
    patient_count,
    success_rate,
    average_patient_satisfaction,
    PERCENT_RANK() OVER (
        PARTITION BY procedure_type 
        ORDER BY success_rate
    ) AS procedure_success_percentile,
    PERCENT_RANK() OVER (
        PARTITION BY procedure_type 
        ORDER BY average_patient_satisfaction
    ) AS satisfaction_percentile,
    ROUND(100 * PERCENT_RANK() OVER (
        PARTITION BY procedure_type 
        ORDER BY success_rate
    ), 2) AS success_percentile_value,
    ROUND(100 * PERCENT_RANK() OVER (
        PARTITION BY procedure_type 
        ORDER BY average_patient_satisfaction
    ), 2) AS satisfaction_percentile_value,
    RANK() OVER (
        PARTITION BY procedure_type 
        ORDER BY (success_rate + average_patient_satisfaction) / 2 DESC
    ) AS combined_quality_rank,
    CASE 
        WHEN (PERCENT_RANK() OVER (
            PARTITION BY procedure_type 
            ORDER BY success_rate
        ) + PERCENT_RANK() OVER (
            PARTITION BY procedure_type 
            ORDER BY average_patient_satisfaction
        )) / 2 >= 0.75 THEN 'Excellent'
        WHEN (PERCENT_RANK() OVER (
            PARTITION BY procedure_type 
            ORDER BY success_rate
        ) + PERCENT_RANK() OVER (
            PARTITION BY procedure_type 
            ORDER BY average_patient_satisfaction
        )) / 2 >= 0.50 THEN 'Good'
        WHEN (PERCENT_RANK() OVER (
            PARTITION BY procedure_type 
            ORDER BY success_rate
        ) + PERCENT_RANK() OVER (
            PARTITION BY procedure_type 
            ORDER BY average_patient_satisfaction
        )) / 2 >= 0.25 THEN 'Satisfactory'
        ELSE 'Needs Improvement'
    END AS quality_assessment
FROM hospital_procedure_outcomes
WHERE data_collection_year = 2024
ORDER BY procedure_type, success_rate DESC;

Pattern 6: Real Estate Market Analysis

Property valuation frequently employs percentile ranking:

SELECT 
    property_id,
    property_address,
    neighborhood_id,
    neighborhood_name,
    property_price,
    square_footage,
    price_per_sqft,
    PERCENT_RANK() OVER (
        PARTITION BY neighborhood_id 
        ORDER BY property_price
    ) AS neighborhood_price_percentile,
    PERCENT_RANK() OVER (
        PARTITION BY neighborhood_id 
        ORDER BY price_per_sqft
    ) AS neighborhood_price_per_sqft_percentile,
    PERCENT_RANK() OVER (
        ORDER BY property_price
    ) AS city_wide_price_percentile,
    ROUND(100 * PERCENT_RANK() OVER (
        PARTITION BY neighborhood_id 
        ORDER BY property_price
    ), 2) AS neighborhood_percentile_value,
    CASE 
        WHEN PERCENT_RANK() OVER (
            PARTITION BY neighborhood_id 
            ORDER BY property_price
        ) <= 0.25 THEN 'Budget Property'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY neighborhood_id 
            ORDER BY property_price
        ) <= 0.75 THEN 'Mid-Range Property'
        ELSE 'Premium Property'
    END AS price_category,
    CASE 
        WHEN PERCENT_RANK() OVER (
            PARTITION BY neighborhood_id 
            ORDER BY price_per_sqft
        ) >= 0.75 THEN 'High Value Per Unit'
        WHEN PERCENT_RANK() OVER (
            PARTITION BY neighborhood_id 
            ORDER BY price_per_sqft
        ) >= 0.25 THEN 'Standard Value'
        ELSE 'Economy Value'
    END AS value_assessment
FROM property_listings
WHERE listing_date >= DATEADD(MONTH, -6, GETDATE())
    AND property_status = 'Active'
ORDER BY neighborhood_id, property_price;

Pattern 7: Financial Transaction Risk Scoring

Banks utilize PERCENT_RANK() for transaction classification:

SELECT 
    transaction_id,
    account_id,
    customer_id,
    transaction_date,
    transaction_amount,
    transaction_velocity,
    geographic_anomaly_score,
    merchant_risk_score,
    PERCENT_RANK() OVER (
        PARTITION BY account_id 
        ORDER BY transaction_amount
    ) AS account_amount_percentile,
    PERCENT_RANK() OVER (
        PARTITION BY account_id 
        ORDER BY transaction_velocity
    ) AS account_velocity_percentile,
    PERCENT_RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY geographic_anomaly_score
    ) AS customer_geo_risk_percentile,
    (PERCENT_RANK() OVER (
        PARTITION BY account_id 
        ORDER BY transaction_amount
    ) + PERCENT_RANK() OVER (
        PARTITION BY account_id 
        ORDER BY transaction_velocity
    ) + PERCENT_RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY geographic_anomaly_score
    )) / 3 AS composite_risk_percentile,
    CASE 
        WHEN (PERCENT_RANK() OVER (
            PARTITION BY account_id 
            ORDER BY transaction_amount
        ) + PERCENT_RANK() OVER (
            PARTITION BY account_id 
            ORDER BY transaction_velocity
        ) + PERCENT_RANK() OVER (
            PARTITION BY customer_id 
            ORDER BY geographic_anomaly_score
        )) / 3 >= 0.75 THEN 'High Risk'
        WHEN (PERCENT_RANK() OVER (
            PARTITION BY account_id 
            ORDER BY transaction_amount
        ) + PERCENT_RANK() OVER (
            PARTITION BY account_id 
            ORDER BY transaction_velocity
        ) + PERCENT_RANK() OVER (
            PARTITION BY customer_id 
            ORDER BY geographic_anomaly_score
        )) / 3 >= 0.50 THEN 'Medium Risk'
        ELSE 'Standard Risk'
    END AS risk_classification
FROM financial_transactions
WHERE transaction_timestamp >= DATEADD(DAY, -30, GETDATE())
ORDER BY account_id, transaction_amount DESC;

Mathematical Comparison Against Related Functions

PERCENT_RANK() vs. CUME_DIST() Detailed Analysis

These functions compute fundamentally different metrics:

SELECT 
    salary,
    RANK() OVER (ORDER BY salary) AS rank_value,
    PERCENT_RANK() OVER (ORDER BY salary) AS percent_rank_value,
    CUME_DIST() OVER (ORDER BY salary) AS cume_dist_value,
    NTILE(4) OVER (ORDER BY salary) AS quartile,
    ROW_NUMBER() OVER (ORDER BY salary) AS row_num
FROM employee_salaries
ORDER BY salary;

With sample data [40K, 50K, 50K, 60K, 70K]:

Salary  RANK  PERCENT_RANK  CUME_DIST  NTILE  ROW_NUMBER
40K     1     0.0           0.2        1      1
50K     2     0.25          0.6        1      2
50K     2     0.25          0.6        2      3
60K     4     0.75          0.8        3      4
70K     5     1.0           1.0        4      5

Key observations:

PERCENT_RANK() computes (rank — 1) / (total — 1), creating gaps for tied values. The second and third rows both rank 2nd, receiving 0.25, but the fourth row jumps to 0.75.

CUME_DIST() computes row count up to current / total rows. Values receiving the same rank position also share identical CUME_DIST() values, but the next distinct value increments based on row count, not rank.

NTILE(4) divides data into four equal-width buckets, assigning quartile numbers.

Precise Mathematical Formulation

PERCENT_RANK(current_row) = (RANK(current_row) - 1) / (Total_Rows - 1)

For tied values at rank 2 in dataset of 5:
= (2 - 1) / (5 - 1)
= 1 / 4
= 0.25

For final value at rank 5 in dataset of 5:
= (5 - 1) / (5 - 1)
= 4 / 4
= 1.0

For first value at rank 1 in dataset of 5:
= (1 - 1) / (5 - 1)
= 0 / 4
= 0.0

PySpark Implementation

from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import (
    col, percent_rank, rank, cume_dist,
    round as spark_round, when, count,
    ntile, row_number, lag, lead
)
import pyspark.sql.functions as F

spark = SparkSession.builder \
    .appName("PercentRankAnalysis") \
    .config("spark.sql.adaptive.enabled", "true") \
    .getOrCreate()

Pattern 1: Basic Performance Ranking

# Employee performance data
employee_data = [
    ("E001", "Alice", "Engineering", 85),
    ("E002", "Bob", "Engineering", 92),
    ("E003", "Charlie", "Engineering", 78),
    ("E004", "David", "Sales", 88),
    ("E005", "Eve", "Sales", 95),
    ("E006", "Frank", "Sales", 82),
    ("E007", "Grace", "Marketing", 87),
    ("E008", "Henry", "Marketing", 91),
]

df_employees = spark.createDataFrame(
    employee_data,
    ["emp_id", "emp_name", "department", "performance_score"]
)
# Define window specifications
dept_window = Window.partitionBy("department") \
    .orderBy(col("performance_score").desc())
company_window = Window.orderBy(col("performance_score").desc())
# Apply PERCENT_RANK()
result = df_employees.select(
    col("emp_id"),
    col("emp_name"),
    col("department"),
    col("performance_score"),
    percent_rank().over(dept_window).alias("dept_percent_rank"),
    spark_round(percent_rank().over(dept_window) * 100, 2).alias("dept_percentile"),
    percent_rank().over(company_window).alias("company_percent_rank"),
    spark_round(percent_rank().over(company_window) * 100, 2).alias("company_percentile"),
    rank().over(dept_window).alias("dept_rank"),
    when(
        percent_rank().over(dept_window) >= 0.667,
        "Top Tier"
    ).when(
        percent_rank().over(dept_window) >= 0.333,
        "Middle Tier"
    ).otherwise("Development Tier").alias("performance_tier")
).orderBy("department", col("performance_score").desc())
result.show(truncate=False)

Output:

+------+--------+-------------+------------------+------------------+--------------+-------------------+-----------+---------+----------------+
|emp_id|emp_name|department   |performance_score |dept_percent_rank |dept_percentile|company_percent_rank|company_percentile|dept_rank|performance_tier|
+------+--------+-------------+------------------+--------------+---+--+--------+---+
|E002  |Bob     |Engineering  |92                |0.5           |50.0|0.625   |62.5|1  |Top Tier    |
|E001  |Alice   |Engineering  |85                |0.25          |25.0|0.375   |37.5|2  |Middle Tier |
|E003  |Charlie |Engineering  |78                |0.0           |0.0 |0.125   |12.5|3  |Development |
|E005  |Eve     |Sales        |95                |1.0           |100.0|1.0    |100.0|1  |Top Tier    |
|E004  |David   |Sales        |88                |0.5           |50.0|0.625   |62.5|2  |Top Tier    |
|E006  |Frank   |Sales        |82                |0.0           |0.0 |0.25    |25.0|3  |Development |
|E008  |Henry   |Marketing    |91                |1.0           |100.0|0.875   |87.5|1  |Top Tier    |
|E007  |Grace   |Marketing    |87                |0.0           |0.0 |0.5     |50.0|2  |Middle Tier |
+------+--------+-------------+------------------+--------------+---+--+--------+---+

Pattern 2: Sales Performance with Comparative Metrics

# Sales data across quarters and regions
sales_data = [
    ("Q1", "North", "REP001", "Alice", 45000),
    ("Q1", "North", "REP002", "Bob", 52000),
    ("Q1", "North", "REP003", "Charlie", 38000),
    ("Q1", "South", "REP004", "David", 48000),
    ("Q1", "South", "REP005", "Eve", 55000),
    ("Q2", "North", "REP001", "Alice", 50000),
    ("Q2", "North", "REP002", "Bob", 48000),
    ("Q2", "North", "REP003", "Charlie", 42000),
    ("Q2", "South", "REP004", "David", 52000),
    ("Q2", "South", "REP005", "Eve", 58000),
]

df_sales = spark.createDataFrame(
    sales_data,
    ["quarter", "region", "rep_id", "rep_name", "revenue"]
)
# Multiple window specifications
regional_window = Window.partitionBy("quarter", "region") \
    .orderBy(col("revenue").desc())
quarterly_window = Window.partitionBy("quarter") \
    .orderBy(col("revenue").desc())
rep_window = Window.partitionBy("rep_id") \
    .orderBy("quarter")
# Comprehensive analysis
analysis = df_sales.select(
    col("quarter"),
    col("region"),
    col("rep_id"),
    col("rep_name"),
    col("revenue"),
    rank().over(regional_window).alias("regional_rank"),
    percent_rank().over(regional_window).alias("regional_percent_rank"),
    spark_round(percent_rank().over(regional_window) * 100, 2).alias("regional_percentile"),
    percent_rank().over(quarterly_window).alias("quarterly_percent_rank"),
    spark_round(percent_rank().over(quarterly_window) * 100, 2).alias("quarterly_percentile"),
    cume_dist().over(regional_window).alias("regional_cume_dist"),
    lag(col("revenue")).over(rep_window).alias("previous_quarter_revenue"),
    (col("revenue") - lag(col("revenue")).over(rep_window)).alias("qoq_change"),
    spark_round(
        ((col("revenue") - lag(col("revenue")).over(rep_window)) / 
         lag(col("revenue")).over(rep_window) * 100), 2
    ).alias("qoq_change_pct"),
    when(
        percent_rank().over(regional_window) >= 0.667,
        "Regional Star"
    ).when(
        percent_rank().over(regional_window) >= 0.333,
        "Solid Performer"
    ).otherwise("Development Focus").alias("regional_status")
).orderBy("quarter", "region", col("revenue").desc())
analysis.show(truncate=False)

Pattern 3: Student Academic Ranking

# Student GPA data
student_data = [
    ("2024", "M001", "CS", "Alice", 3.95),
    ("2024", "M001", "CS", "Bob", 3.87),
    ("2024", "M001", "CS", "Charlie", 3.72),
    ("2024", "M001", "CS", "David", 3.65),
    ("2024", "M002", "EE", "Eve", 3.92),
    ("2024", "M002", "EE", "Frank", 3.81),
    ("2024", "M002", "EE", "Grace", 3.58),
    ("2024", "M003", "ME", "Henry", 3.89),
    ("2024", "M003", "ME", "Iris", 3.76),
]

df_students = spark.createDataFrame(
    student_data,
    ["academic_year", "major_id", "major_name", "student_name", "cumulative_gpa"]
)
# Window specifications
major_window = Window.partitionBy("academic_year", "major_id") \
    .orderBy(col("cumulative_gpa").desc())
university_window = Window.partitionBy("academic_year") \
    .orderBy(col("cumulative_gpa").desc())
# Academic ranking
academic_ranking = df_students.select(
    col("academic_year"),
    col("major_id"),
    col("major_name"),
    col("student_name"),
    col("cumulative_gpa"),
    rank().over(major_window).alias("major_rank"),
    percent_rank().over(major_window).alias("major_percent_rank"),
    spark_round(percent_rank().over(major_window) * 100, 2).alias("major_percentile_score"),
    rank().over(university_window).alias("university_rank"),
    percent_rank().over(university_window).alias("university_percent_rank"),
    spark_round(percent_rank().over(university_window) * 100, 2).alias("university_percentile"),
    count("*").over(Window.partitionBy("academic_year", "major_id")).alias("students_in_major"),
    when(
        percent_rank().over(major_window) <= 0.1,
        "Summa Cum Laude"
    ).when(
        percent_rank().over(major_window) <= 0.25,
        "Magna Cum Laude"
    ).when(
        percent_rank().over(major_window) <= 0.5,
        "Cum Laude"
    ).otherwise("Standard Honors").alias("honors_designation")
).orderBy("academic_year", "major_id", col("cumulative_gpa").desc())
academic_ranking.show(truncate=False)

Pattern 4: Financial Risk Scoring

# Transaction risk assessment data
transaction_data = [
    ("ACC001", "CUST001", "2024-01-15", 5000, 8.5, 3.2),
    ("ACC001", "CUST001", "2024-01-16", 8500, 9.1, 2.1),
    ("ACC001", "CUST001", "2024-01-17", 3200, 5.5, 1.8),
    ("ACC002", "CUST002", "2024-01-15", 2100, 6.8, 4.5),
    ("ACC002", "CUST002", "2024-01-16", 4500, 7.2, 3.9),
    ("ACC002", "CUST002", "2024-01-17", 6800, 8.9, 5.2),
    ("ACC003", "CUST003", "2024-01-15", 1500, 4.2, 2.1),
    ("ACC003", "CUST003", "2024-01-16", 3500, 6.5, 3.8),
]

df_transactions = spark.createDataFrame(
    transaction_data,
    ["account_id", "customer_id", "transaction_date", "amount", "velocity_score", "anomaly_score"]
)
# Risk assessment windows
account_window = Window.partitionBy("account_id") \
    .orderBy(col("amount").desc())
customer_window = Window.partitionBy("customer_id") \
    .orderBy(col("anomaly_score").desc())
# Comprehensive risk analysis
risk_assessment = df_transactions.select(
    col("account_id"),
    col("customer_id"),
    col("transaction_date"),
    col("amount"),
    col("velocity_score"),
    col("anomaly_score"),
    percent_rank().over(account_window).alias("account_amount_percentile"),
    spark_round(percent_rank().over(account_window) * 100, 2).alias("amount_percentile_value"),
    percent_rank().over(
        Window.partitionBy("account_id").orderBy(col("velocity_score").desc())
    ).alias("account_velocity_percentile"),
    percent_rank().over(customer_window).alias("customer_anomaly_percentile"),
    spark_round(
        (percent_rank().over(account_window) + 
         percent_rank().over(Window.partitionBy("account_id").orderBy(col("velocity_score").desc())) +
         percent_rank().over(customer_window)) / 3, 2
    ).alias("composite_risk_score"),
    when(
        (percent_rank().over(account_window) + 
         percent_rank().over(Window.partitionBy("account_id").orderBy(col("velocity_score").desc())) +
         percent_rank().over(customer_window)) / 3 >= 0.75,
        "High Risk"
    ).when(
        (percent_rank().over(account_window) + 
         percent_rank().over(Window.partitionBy("account_id").orderBy(col("velocity_score").desc())) +
         percent_rank().over(customer_window)) / 3 >= 0.5,
        "Medium Risk"
    ).otherwise("Standard Risk").alias("risk_level")
).orderBy("account_id", col("amount").desc())
risk_assessment.show(truncate=False)

Pattern 5: Product Market Analysis

# Product market share data
product_data = [
    ("Electronics", "PROD001", "Laptop Pro", 28.5, 15.2),
    ("Electronics", "PROD002", "Tablet Plus", 22.3, 12.8),
    ("Electronics", "PROD003", "Phone Max", 19.7, 11.5),
    ("Electronics", "PROD004", "Smartwatch", 15.8, 9.2),
    ("Electronics", "PROD005", "Headphones", 10.2, 6.8),
    ("Clothing", "PROD006", "Premium Jeans", 24.5, 18.3),
    ("Clothing", "PROD007", "Designer Shirt", 19.8, 15.1),
    ("Clothing", "PROD008", "Athletic Wear", 17.2, 13.9),
    ("Clothing", "PROD009", "Casual Dress", 12.5, 9.7),
]

df_products = spark.createDataFrame(
    product_data,
    ["category", "product_id", "product_name", "market_share_pct", "growth_rate_pct"]
)
# Market analysis windows
category_market_window = Window.partitionBy("category") \
    .orderBy(col("market_share_pct").desc())
category_growth_window = Window.partitionBy("category") \
    .orderBy(col("growth_rate_pct").desc())
overall_window = Window.orderBy(col("market_share_pct").desc())
# Market analysis
market_analysis = df_products.select(
    col("category"),
    col("product_id"),
    col("product_name"),
    col("market_share_pct"),
    col("growth_rate_pct"),
    rank().over(category_market_window).alias("category_market_rank"),
    percent_rank().over(category_market_window).alias("category_market_percentile"),
    spark_round(percent_rank().over(category_market_window) * 100, 2).alias("market_percentile_score"),
    percent_rank().over(category_growth_window).alias("category_growth_percentile"),
    percent_rank().over(overall_window).alias("overall_market_percentile"),
    cume_dist().over(category_market_window).alias("category_cumulative_share"),
    when(
        percent_rank().over(category_market_window) >= 0.667,
        "Market Leader"
    ).when(
        percent_rank().over(category_market_window) >= 0.333,
        "Strong Contender"
    ).otherwise("Emerging Product").alias("market_position"),
    when(
        percent_rank().over(category_growth_window) >= 0.667,
        "High Growth"
    ).otherwise("Steady Performance").alias("growth_trajectory")
).orderBy("category", col("market_share_pct").desc())
market_analysis.show(truncate=False)

Pattern 6: Real Estate Property Valuation

# Property valuation data
property_data = [
    ("Downtown", "PROP001", 750000, 3500),
    ("Downtown", "PROP002", 685000, 3200),
    ("Downtown", "PROP003", 620000, 2900),
    ("Downtown", "PROP004", 550000, 2500),
    ("Suburban", "PROP005", 450000, 2800),
    ("Suburban", "PROP006", 380000, 2300),
    ("Suburban", "PROP007", 320000, 1900),
    ("Suburban", "PROP008", 280000, 1600),
    ("Rural", "PROP009", 250000, 2200),
    ("Rural", "PROP010", 180000, 1500),
]

df_properties = spark.createDataFrame(
    property_data,
    ["neighborhood", "property_id", "property_price", "square_feet"]
)
# Calculate price per square foot
df_properties = df_properties.withColumn(
    "price_per_sqft",
    F.round(col("property_price") / col("square_feet"), 2)
)
# Market analysis windows
neighborhood_price_window = Window.partitionBy("neighborhood") \
    .orderBy(col("property_price").desc())
neighborhood_ppsqft_window = Window.partitionBy("neighborhood") \
    .orderBy(col("price_per_sqft").desc())
overall_price_window = Window.orderBy(col("property_price").desc())
# Property analysis
property_analysis = df_properties.select(
    col("neighborhood"),
    col("property_id"),
    col("property_price"),
    col("square_feet"),
    col("price_per_sqft"),
    rank().over(neighborhood_price_window).alias("neighborhood_price_rank"),
    percent_rank().over(neighborhood_price_window).alias("neighborhood_price_percentile"),
    spark_round(percent_rank().over(neighborhood_price_window) * 100, 2).alias("price_percentile_score"),
    percent_rank().over(neighborhood_ppsqft_window).alias("neighborhood_ppsqft_percentile"),
    percent_rank().over(overall_price_window).alias("city_wide_price_percentile"),
    when(
        percent_rank().over(neighborhood_price_window) >= 0.667,
        "Premium Property"
    ).when(
        percent_rank().over(neighborhood_price_window) >= 0.333,
        "Mid-Range Property"
    ).otherwise("Budget Property").alias("price_category"),
    when(
        percent_rank().over(neighborhood_ppsqft_window) >= 0.667,
        "High Value Per Sqft"
    ).when(
        percent_rank().over(neighborhood_ppsqft_window) >= 0.333,
        "Standard Value"
    ).otherwise("Economy Value").alias("value_assessment")
).orderBy("neighborhood", col("property_price").desc())
property_analysis.show(truncate=False)

Pattern 7: Detecting Performance Anomalies

from pyspark.sql.functions import stddev, avg, abs

# Customer behavior data
customer_data = [
    ("CUST001", "2024-01-15", 150.50),
    ("CUST001", "2024-01-16", 145.75),
    ("CUST001", "2024-01-17", 5000.00),
    ("CUST002", "2024-01-15", 89.99),
    ("CUST002", "2024-01-16", 92.50),
    ("CUST002", "2024-01-17", 1500.00),
    ("CUST003", "2024-01-15", 250.00),
    ("CUST003", "2024-01-16", 265.75),
    ("CUST003", "2024-01-17", 245.50),
]
df_customer_orders = spark.createDataFrame(
    customer_data,
    ["customer_id", "order_date", "order_amount"]
)
# Anomaly detection window
customer_window = Window.partitionBy("customer_id")
# Detect anomalies using percentile ranking
anomaly_detection = df_customer_orders.select(
    col("customer_id"),
    col("order_date"),
    col("order_amount"),
    avg(col("order_amount")).over(customer_window).alias("customer_avg_order"),
    stddev(col("order_amount")).over(customer_window).alias("customer_std_dev"),
    percent_rank().over(
        Window.partitionBy("customer_id").orderBy(col("order_amount").desc())
    ).alias("order_amount_percentile"),
    when(
        percent_rank().over(
            Window.partitionBy("customer_id").orderBy(col("order_amount").desc())
        ) >= 0.75,
        "Unusually High"
    ).when(
        percent_rank().over(
            Window.partitionBy("customer_id").orderBy(col("order_amount").desc())
        ) <= 0.25,
        "Unusually Low"
    ).otherwise("Normal Range").alias("order_anomaly_status")
).orderBy("customer_id", col("order_amount").desc())
anomaly_detection.show(truncate=False)

Pattern 8: Performance Improvement Tracking

# Employee performance over time
performance_data = [
    ("E001", "Q1-2024", 75),
    ("E001", "Q2-2024", 78),
    ("E001", "Q3-2024", 82),
    ("E001", "Q4-2024", 88),
    ("E002", "Q1-2024", 82),
    ("E002", "Q2-2024", 80),
    ("E002", "Q3-2024", 79),
    ("E002", "Q4-2024", 77),
    ("E003", "Q1-2024", 70),
    ("E003", "Q2-2024", 75),
    ("E003", "Q3-2024", 80),
    ("E003", "Q4-2024", 85),
]

df_performance = spark.createDataFrame(
    performance_data,
    ["emp_id", "quarter", "performance_score"]
)
# Windows for comparative analysis
emp_window_temporal = Window.partitionBy("emp_id").orderBy("quarter")
emp_window_ranking = Window.partitionBy("emp_id").orderBy(col("performance_score").desc())
quarter_window = Window.partitionBy("quarter").orderBy(col("performance_score").desc())
# Performance improvement analysis
improvement_analysis = df_performance.select(
    col("emp_id"),
    col("quarter"),
    col("performance_score"),
    lag(col("performance_score")).over(emp_window_temporal).alias("previous_quarter_score"),
    (col("performance_score") - lag(col("performance_score")).over(emp_window_temporal)).alias("qoq_change"),
    percent_rank().over(emp_window_ranking).alias("employee_historical_percentile"),
    percent_rank().over(quarter_window).alias("quarterly_peer_percentile"),
    spark_round(percent_rank().over(quarter_window) * 100, 2).alias("quarterly_percentile_score"),
    when(
        col("performance_score") > lag(col("performance_score")).over(emp_window_temporal),
        "Improving"
    ).when(
        col("performance_score") < lag(col("performance_score")).over(emp_window_temporal),
        "Declining"
    ).otherwise("Stable").alias("trend_direction"),
    when(
        percent_rank().over(quarter_window) >= 0.667,
        "Top Performer This Quarter"
    ).when(
        percent_rank().over(quarter_window) >= 0.333,
        "Middle Performer This Quarter"
    ).otherwise("Below Average This Quarter").alias("quarterly_status")
).orderBy("emp_id", "quarter")
improvement_analysis.show(truncate=False)

Real-World Azure Implementation Scenarios

Scenario 1: Azure Synapse Analytics Integration

from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import col, percent_rank, round as spark_round

spark = SparkSession.builder \
    .appName("SynapsePercentRankAnalysis") \
    .getOrCreate()
# Connect to Azure Synapse
synapse_url = "jdbc:sqlserver://workspacename.sql.azuresynapse.net:1433" \
              ";database=database_name" \
              ";encrypt=true;trustServerCertificate=false" \
              ";loginTimeout=30;"
properties = {
    "user": "username@workspacename",
    "password": "password",
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}
# Read data from Synapse
sales_data = spark.read \
    .jdbc(synapse_url, "dbo.sales_transactions", properties=properties)
# Apply PERCENT_RANK() analysis
window_spec = Window.partitionBy("region", "product_category") \
    .orderBy(col("sales_amount").desc())
enriched_analysis = sales_data.select(
    col("*"),
    percent_rank().over(window_spec).alias("sales_percentile"),
    spark_round(percent_rank().over(window_spec) * 100, 2).alias("percentile_rank")
)
# Write results back to Synapse
enriched_analysis.write \
    .jdbc(synapse_url, "dbo.sales_percentile_analysis", 
          mode="overwrite", properties=properties)
print("Percent rank analysis completed and written to Synapse")

Scenario 2: Azure Data Lake with Delta Tables

from delta.tables import DeltaTable
from pyspark.sql.window import Window
from pyspark.sql.functions import col, percent_rank, round as spark_round

# Read from Delta Lake
delta_path = "abfss://analytics@datalakename.dfs.core.windows.net/processed/customer_transactions/"
df_transactions = spark.read.format("delta").load(delta_path)
# Apply complex PERCENT_RANK() analysis
window_customer = Window.partitionBy("customer_id") \
    .orderBy(col("transaction_amount").desc())
window_product = Window.partitionby("product_category") \
    .orderBy(col("transaction_amount").desc())
window_temporal = Window.partitionBy("transaction_month") \
    .orderBy(col("transaction_amount").desc())
analysis = df_transactions.select(
    col("customer_id"),
    col("product_category"),
    col("transaction_month"),
    col("transaction_amount"),
    percent_rank().over(window_customer).alias("customer_percentile"),
    percent_rank().over(window_product).alias("product_percentile"),
    percent_rank().over(window_temporal).alias("monthly_percentile"),
    spark_round(
        (percent_rank().over(window_customer) + 
         percent_rank().over(window_product) +
         percent_rank().over(window_temporal)) / 3, 3
    ).alias("composite_percentile")
)
# Write enriched results back to Delta
output_path = "abfss://analytics@datalakename.dfs.core.windows.net/analysis/percentile_rankings/"
analysis.write.format("delta").mode("overwrite") \
    .option("overwriteSchema", "true") \
    .save(output_path)
print(f"Analysis saved to {output_path}")

Scenario 3: Azure Databricks Cluster Optimization

from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import col, percent_rank, round as spark_round, broadcast

spark = SparkSession.builder \
    .appName("OptimizedPercentRankAnalysis") \
    .config("spark.sql.adaptive.coalescePartitions.enabled", "true") \
    .config("spark.sql.adaptive.skewJoin.enabled", "true") \
    .getOrCreate()
# Read large fact table
fact_table = spark.read.parquet(
    "abfss://data@account.dfs.core.windows.net/facts/"
)
# Read smaller dimension table with broadcast
dim_table = spark.read.parquet(
    "abfss://data@account.dfs.core.windows.net/dimensions/"
)
# Join with broadcast optimization
joined = fact_table.join(
    broadcast(dim_table),
    on="dimension_id"
)
# Apply PERCENT_RANK() with strategic partitioning
strategic_partitions = joined.repartition("region", "product_category")
window_spec = Window.partitionBy("region", "product_category") \
    .orderBy(col("metric_value").desc())
analysis = strategic_partitions.select(
    col("*"),
    percent_rank().over(window_spec).alias("percentile_rank"),
    spark_round(percent_rank().over(window_spec) * 100, 2).alias("percentile_score")
)
# Cache for multiple downstream operations
analysis.cache()
# Generate multiple derived outputs
summary = analysis.groupBy("region") \
    .agg(
        F.avg("percentile_score").alias("avg_percentile"),
        F.max("percentile_score").alias("max_percentile"),
        F.min("percentile_score").alias("min_percentile")
    )
# Execute analyses
summary.show()
analysis.unpersist()

Performance Optimization Strategies

Strategy 1: Partition Cardinality Optimization

from pyspark.sql.functions import col, percent_rank, round as spark_round

# Analyze partition characteristics
partition_stats = df.groupBy("partition_column").count() \
    .rdd.map(lambda x: (x[0], x[1])) \
    .collect()
print("Partition cardinality analysis:")
for partition, count in partition_stats:
    print(f"{partition}: {count} rows")
# If high-cardinality partitions exist, filter strategically
filtered_df = df.filter(col("status").isin(["Active", "Pending"]))
# Then apply PERCENT_RANK()
window_spec = Window.partitionBy("partition_column") \
    .orderBy(col("metric").desc())
result = filtered_df.select(
    col("*"),
    percent_rank().over(window_spec).alias("percentile_rank")
)

Strategy 2: Intermediate Aggregation

# Pre-aggregate data before windowing
pre_aggregated = df.groupBy("customer_id", "product_id") \
    .agg(
        F.sum("amount").alias("total_amount"),
        F.count("*").alias("transaction_count"),
        F.avg("amount").alias("average_amount")
    )

# Now window on smaller aggregated dataset
window_spec = Window.partitionBy("customer_id") \
    .orderBy(col("total_amount").desc())
result = pre_aggregated.select(
    col("*"),
    percent_rank().over(window_spec).alias("customer_percentile")
)

Strategy 3: Materialized Window Results

# Materialize frequently-used window calculations
materialized = df.select(
    col("*"),
    percent_rank().over(
        Window.partitionBy("category").orderBy(col("value").desc())
    ).alias("category_percentile"),
    percent_rank().over(
        Window.orderBy(col("value").desc())
    ).alias("overall_percentile")
).cache()

# Derive multiple insights from cached results without recalculation
insight_1 = materialized.filter(col("category_percentile") >= 0.75)
insight_2 = materialized.filter(col("overall_percentile") < 0.25)
insight_3 = materialized.select(
    col("category"),
    (col("category_percentile") * 100).alias("percentile_score")
)
insight_1.write.parquet("path1")
insight_2.write.parquet("path2")
insight_3.write.parquet("path3")
materialized.unpersist()

Edge Cases and Best Practices

Handling Single-Value Partitions

# Single-row partitions produce predictable results
single_row_test = df.filter(col("partition_key") == "SINGLE_VALUE")

result = single_row_test.select(
    col("*"),
    percent_rank().over(window_spec).alias("percentile_rank")
)
# Result: 0.0 for single row (rank 1, so (1-1)/(1-1) = 0/0 -> 0)

NULL Value Handling

# PERCENT_RANK() ignores NULL values in ORDER BY column
null_handling = df.select(
    col("*"),
    percent_rank().over(
        Window.partitionBy("category").orderBy(col("nullable_metric").desc())
    ).alias("percentile_rank")
).filter(col("nullable_metric").isNotNull())

Deterministic Ordering

# Ensure deterministic results with multi-column ordering
deterministic_window = Window.partitionBy("category") \
    .orderBy(col("primary_metric").desc(), col("secondary_metric").desc(), col("id"))

result = df.select(
    col("*"),
    percent_rank().over(deterministic_window).alias("percentile_rank")
)

Comprehensive Comparison Table

Aspect PERCENT_RANK() CUME_DIST() RANK() ROW_NUMBER() NTILE(n) Calculation (rank-1)/(total-1) count<=current/total Ordinal with gaps Sequential Equal buckets Returns 0 to 1 0 to 1 Integer with gaps Integers Bucket numbers First Row 0.0 1/n 1 1 1 Last Row 1.0 1.0 n n n Tied Values Identical Identical Identical Different May vary Use Case Percentile ranking Distribution position Ordinal ranking Row enumeration Quartile assignment Mathematical Nature Percentile-based Count-based Position-based Position-based Bucket-based

Production Implementation Checklist

Before deploying PERCENT_RANK() in production environments:

1. Validate Ordering Logic Ensure ORDER BY clauses uniquely define row sequence. Multiple rows with identical ordering values produce non-deterministic results.

2. Confirm Partition Cardinality Assess whether partition sizes align with performance requirements. Extremely large partitions impact calculation speed.

3. Implement Caching Strategically Materialize window calculations if referenced multiple times in downstream operations.

4. Monitor Null Handling Verify that NULL values in ORDER BY columns are handled as expected (typically excluded from ranking).

5. Test Edge Cases Validate behavior with single-row partitions, all-identical values, and complete NULL columns.

6. Document Interpretation Ensure stakeholders understand PERCENT_RANK() semantics and interpretation for business applications.

Conclusion

PERCENT_RANK() provides sophisticated percentile-based analytical capabilities that address requirements distinct from both ordinal ranking and cumulative distribution approaches. The function calculates relative standing using mathematically rigorous formulations that yield values between 0 and 1 inclusive, with boundaries at the minimum and maximum positions. The ability to compute percentile rankings while maintaining row-level detail, across multiple partitioning schemes simultaneously, addresses analytical requirements that prove invaluable in competitive analysis, performance management, and distribution assessment scenarios.