Lakehouse Ranking and Percentile Analysis Guide

Overview

Ranking and percentile analysis are advanced scenarios in data analysis, used to compute the relative position of data within groups, percentile scores, and more. Singdata Lakehouse provides comprehensive window function support, including ranking functions, offset functions, and aggregate window functions. This guide categorizes usage by business scenario to help you quickly master efficient ranking and percentile analysis methods.

SQL Commands Covered

Command/FunctionPurposeApplicable Scenario
ROW_NUMBER()Generate unique row numbersDeduplication, pagination, Top-N
RANK()Ranking (ties skip numbers)Score ranking, competition ranking
DENSE_RANK()Ranking (ties don't skip numbers)Continuous ranking
PERCENT_RANK()Percentile rankingRelative position analysis
LAG() / LEAD()Access previous/next row dataPeriod-over-period, year-over-year
AVG() OVER (... ROWS BETWEEN)Moving averageTrend analysis
PERCENTILE()Quantile calculationMedian, quartiles

Prerequisites

The following examples use a simulated employee performance table performance:

-- Create test table
CREATE TABLE IF NOT EXISTS performance (
    emp_id INT,
    emp_name STRING,
    dept STRING,
    score DOUBLE,
    review_date DATE
);

-- Insert test data
INSERT INTO performance VALUES
(1, 'Alice', 'Engineering', 95, '2024-06-01'),
(2, 'Bob', 'Engineering', 85, '2024-06-01'),
(3, 'Carol', 'Engineering', 95, '2024-06-01'),
(4, 'David', 'Marketing', 88, '2024-06-01'),
(5, 'Eve', 'Marketing', 92, '2024-06-01'),
(6, 'Frank', 'Marketing', 78, '2024-06-01'),
(7, 'Grace', 'HR', 90, '2024-06-01'),
(8, 'Henry', 'HR', 85, '2024-06-01');

Basic Ranking

Use ranking functions to compute employee performance rankings within departments. Lakehouse provides three ranking functions with slightly different behaviors.

-- Comparison of three ranking functions
SELECT 
    emp_name,
    dept,
    score,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY score DESC) as row_num,
    RANK() OVER (PARTITION BY dept ORDER BY score DESC) as rank,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY score DESC) as dense_rank
FROM performance
ORDER BY dept, row_num;

Result:

emp_namedeptscorerow_numrankdense_rank
AliceEngineering95111
CarolEngineering95211
BobEngineering85332
EveMarketing92111
DavidMarketing88222
FrankMarketing78333
GraceHR90111
HenryHR85222

Differences Between Ranking Functions

FunctionTie HandlingNumber Continuity
ROW_NUMBER()Randomly assigns different numbersContinuous
RANK()Same rank for ties, subsequent numbers skipDiscontinuous (1,1,3)
DENSE_RANK()Same rank for ties, subsequent numbers are continuousContinuous (1,1,2)

Percentile Ranking

Use PERCENT_RANK() to compute an employee's relative position within the department (between 0 and 1).

-- Compute percentile ranking
SELECT 
    emp_name,
    dept,
    score,
    ROUND(PERCENT_RANK() OVER (PARTITION BY dept ORDER BY score DESC), 2) as pct_rank
FROM performance
ORDER BY dept, pct_rank DESC;

Result:

emp_namedeptscorepct_rank
AliceEngineering950
CarolEngineering950
BobEngineering850.5
EveMarketing920
DavidMarketing880.5
FrankMarketing781
GraceHR900
HenryHR851

Before/After Row Comparison

Use LAG() and LEAD() to access the previous or next row's data relative to the current row, commonly used for period-over-period change calculations.

-- Compute score differences between employees within departments
SELECT 
    emp_name,
    dept,
    score,
    LAG(score, 1) OVER (PARTITION BY dept ORDER BY score DESC) as prev_score,
    score - LAG(score, 1) OVER (PARTITION BY dept ORDER BY score DESC) as diff
FROM performance
ORDER BY dept, score DESC;

Result:

emp_namedeptscoreprev_scorediff
AliceEngineering95NULLNULL
CarolEngineering95950
BobEngineering8595-10
EveMarketing92NULLNULL
DavidMarketing8892-4
FrankMarketing7888-10
GraceHR90NULLNULL
HenryHR8590-5

Moving Average

Use window frames (ROWS BETWEEN) to compute sliding window statistics, suitable for trend analysis.

-- Compute moving average within department ordered by score (current row and two preceding rows)
SELECT 
    emp_name,
    dept,
    score,
    ROUND(AVG(score) OVER (
        PARTITION BY dept 
        ORDER BY score DESC 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 1) as moving_avg_3
FROM performance
ORDER BY dept, score DESC;

Result:

emp_namedeptscoremoving_avg_3
AliceEngineering9595
CarolEngineering9595
BobEngineering8591.7
EveMarketing9292
DavidMarketing8890
FrankMarketing7886
GraceHR9090
HenryHR8587.5

Quantile Calculation

Use the PERCENTILE() function to compute data quantiles such as the median (0.5) and quartiles (0.25, 0.75).

-- Compute median and quartiles of scores by department
SELECT 
    dept,
    PERCENTILE(score, 0.5) as median,
    PERCENTILE(score, 0.25) as q1,
    PERCENTILE(score, 0.75) as q3,
    MIN(score) as min_score,
    MAX(score) as max_score
FROM performance
GROUP BY dept
ORDER BY dept;

Result:

deptmedianq1q3min_scoremax_score
Engineering9590958595
HR87.586.2588.758590
Marketing8883907892

Clean Up Test Data

After completing ranking analysis verification, it is recommended to clean up test tables:

-- Drop test table
DROP TABLE IF EXISTS performance;

Important Notes

  1. Window Function Execution Order: Window functions execute after WHERE and GROUP BY; window function results cannot be used directly in WHERE. Use QUALIFY or subqueries to filter.
  2. NULL Value Sorting: ORDER BY places NULL values last (DESC) or first (ASC) by default; use NULLS FIRST/LAST to control.
  3. Performance Optimization: Choose PARTITION BY columns with moderate cardinality to avoid individual partitions becoming too large.
  4. PERCENT_RANK Syntax: PERCENT_RANK() does not accept arguments; write PERCENT_RANK() OVER (...) directly.
Data Grouping and Aggregation
Time Series Analysis
Singdata © 2024 Singdata, Inc. All Rights Reserved.