Day 17 of 32 Days of SQL Concepts — Analytic Functions

Analytic functions represent a sophisticated analytical capability that fundamentally transforms SQL from a set-oriented language into a…

Karthik · 2026-06-06 18:19 · 1 claps · 16.5 min read

#technology #data-engineering #software-engineering #medium #writing

Open on Medium ↗

Wiki topics: EVAL · Evaluation & Benchmarks 🔧 · Data Engineering

Day 17 of 32 Days of SQL Concepts — Analytic Functions

Analytic functions represent a sophisticated analytical capability that fundamentally transforms SQL from a set-oriented language into a sophisticated analytical platform. Unlike aggregate functions that collapse multiple rows into single summary values, analytic functions retain row-level detail while providing windowed calculations across subsets of data. For a data engineer with your background, mastering analytic functions constitutes essential knowledge for building efficient, complex analytical queries without requiring external processing frameworks.

Analytic functions bridge the conceptual gap between traditional SQL aggregation and row-level transformation. They enable sophisticated analytical capabilities including running totals, percent-of-total calculations, ranking within groups, statistical distributions, and temporal comparisons, all within single pass through the data without requiring self-joins or subqueries.

Part One: Foundational Analytic Function Concepts

1.1 Analytic Functions versus Aggregate Functions

The fundamental distinction between these function categories affects query structure, result set composition, and execution efficiency.

Aggregate Functions: Collapse Rows

Aggregate functions reduce multiple rows to single summary values:

-- Aggregate function reduces 1000 orders to 3 regional summaries
SELECT 
    region,
    COUNT(*) as order_count,
    SUM(order_amount) as total_sales,
    AVG(order_amount) as avg_order_value
FROM orders
GROUP BY region;

-- Result: 3 rows (one per region)
-- Detail: Lost (cannot see individual orders)
-- Use case: Summary reporting

Analytic Functions: Retain Rows

Analytic functions maintain row-level detail while providing windowed calculations:

-- Analytic function retains all 1000 orders while calculating regional totals
SELECT 
    order_id,
    region,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY region) as region_total,
    COUNT(*) OVER (PARTITION BY region) as region_order_count,
    AVG(order_amount) OVER (PARTITION BY region) as region_avg_order
FROM orders;

-- Result: 1000 rows (all individual orders retained)
-- Detail: Preserved (each order visible with regional context)
-- Use case: Detailed analysis with contextual aggregates

1.2 OVER Clause: The Analytic Function Specification

The OVER clause defines window boundaries and determines which rows participate in calculations:

-- Basic OVER clause structure
function_name(column) OVER (
    [PARTITION BY partition_columns]
    [ORDER BY sort_columns [ASC|DESC]]
    [ROWS/RANGE frame_specification]
)

-- Components:
-- PARTITION BY: Divides rows into independent windows
-- ORDER BY: Determines sequence within window
-- ROWS/RANGE: Specifies frame boundaries

Partitioning Logic

-- Without PARTITION BY: Single window across entire result set
SELECT 
    order_id,
    order_amount,
    SUM(order_amount) OVER () as grand_total
FROM orders;
-- Result: Every row sees same grand_total

-- With PARTITION BY: Independent windows per partition
SELECT 
    order_id,
    region,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY region) as region_total
FROM orders;
-- Result: Each row sees only region's total

Frame Specifications

-- ROWS frame: Includes specific number of rows
SELECT 
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as three_day_total
FROM orders;
-- Sums current row plus previous 2 rows

-- RANGE frame: Includes all rows within value range
SELECT 
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW
    ) as week_total
FROM orders;
-- Sums all orders within 7 days preceding current date
-- UNBOUNDED frames: Include all preceding or following rows
SELECT 
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as all_orders_total
FROM orders;
-- Sums all orders regardless of date

1.3 Analytic Function Categories

SQL provides numerous analytic functions organized into logical categories.

Analytic Function Categories:

Ranking Functions:
├─ ROW_NUMBER(): Sequential position
├─ RANK(): Position with gaps for ties
├─ DENSE_RANK(): Position without gaps
├─ NTILE(n): Divide into n equal buckets
└─ PERCENT_RANK(): Relative percentile position

Aggregate Functions (Windowed):
├─ SUM(): Cumulative or windowed total
├─ COUNT(): Row count within window
├─ AVG(): Average within window
├─ MIN(): Minimum within window
├─ MAX(): Maximum within window
└─ GROUPING_ID(): Hierarchical grouping identifier

Offset Functions:
├─ LAG(): Access previous row values
├─ LEAD(): Access following row values
├─ FIRST_VALUE(): First row in window
└─ LAST_VALUE(): Last row in window

Statistical Functions:
├─ STDEV(): Standard deviation
├─ VARIANCE(): Variance calculation
├─ STDDEV_POP(): Population standard deviation
└─ VAR_POP(): Population variance

Hypothetical Functions:
├─ RANK(): Rank specific values hypothetically
├─ DENSE_RANK(): Dense rank hypothetically
└─ PERCENT_RANK(): Percentile rank hypothetically

Distribution Functions:
├─ CUME_DIST(): Cumulative distribution
├─ PERCENT_RANK(): Percentile distribution
└─ NTILE(): Equal-width distribution

Part Two: Ranking Analytic Functions

2.1 ROW_NUMBER() Function

ROW_NUMBER assigns sequential integer values regardless of ties:

-- Basic ROW_NUMBER usage
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees
WHERE active_status = 'Active'
ORDER BY salary DESC;

-- Result demonstrates sequential numbering:

/*
Employee  Department   Salary   Salary_Rank
Alice     Sales        95000    1
Bob       Engineering  92000    2
Charlie   Sales        92000    3    (Note: Different rank despite same salary)
David     Finance      88000    4
Eve       Sales        85000    5
*/

-- ROW_NUMBER with partitioning
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_salary_rank,
    ROW_NUMBER() OVER (ORDER BY hire_date ASC) as company_seniority_rank
FROM employees
WHERE active_status = 'Active';
-- Use case: Find most recent transaction per customer
SELECT 
    customer_id,
    customer_name,
    transaction_date,
    transaction_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date DESC) as transaction_recency
FROM transactions
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date DESC) = 1;
-- QUALIFY clause filters to most recent transaction only

2.2 RANK() and DENSE_RANK() Functions

These functions handle tied values differently from ROW_NUMBER:

-- RANK() creates gaps for ties
SELECT 
    employee_id,
    salary,
    RANK() OVER (ORDER BY salary DESC) as salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as dense_salary_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as row_number_rank
FROM employees;

-- Comparison of ranking methods:
/*
Salary   RANK   DENSE_RANK   ROW_NUMBER
95000    1      1            1
92000    2      2            2
92000    2      2            3        (Tied, same RANK/DENSE_RANK, different ROW_NUMBER)
88000    4      3            4        (Gap in RANK after tie)
85000    5      4            5
*/
-- RANK() appropriate for competitive ranking
-- Example: Olympic medals where ties result in skipped ranks
SELECT 
    athlete_name,
    sport,
    points,
    RANK() OVER (PARTITION BY sport ORDER BY points DESC) as sport_rank
FROM competition_results;
-- DENSE_RANK() appropriate for categorical ranking
-- Example: Grading with no gaps
SELECT 
    student_name,
    exam_score,
    DENSE_RANK() OVER (ORDER BY exam_score DESC) as performance_tier
FROM exam_results;
-- Use case: Get top 3 salaries per department (accounting for ties)
WITH ranked_salaries AS (
    SELECT 
        employee_id,
        employee_name,
        department,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
    FROM employees
)
SELECT 
    employee_id,
    employee_name,
    department,
    salary
FROM ranked_salaries
WHERE dept_rank <= 3;

2.3 NTILE(n) Function

NTILE divides result set into approximately equal-sized buckets:

-- Divide employees into quartiles by salary
SELECT 
    employee_id,
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary) as salary_quartile,
    CASE 
        WHEN NTILE(4) OVER (ORDER BY salary) = 1 THEN 'Q1 (Lowest)'
        WHEN NTILE(4) OVER (ORDER BY salary) = 2 THEN 'Q2'
        WHEN NTILE(4) OVER (ORDER BY salary) = 3 THEN 'Q3'
        WHEN NTILE(4) OVER (ORDER BY salary) = 4 THEN 'Q4 (Highest)'
    END as salary_tier
FROM employees;

-- NTILE characteristics:
-- Distributes rows as evenly as possible
-- Remaining rows assigned to first buckets
-- Example: 100 rows into 10 buckets = 10 rows per bucket
-- Example: 103 rows into 10 buckets = 11,11,10,10,10,10,10,10,10,10
-- Decile analysis (10 buckets)
SELECT 
    customer_id,
    lifetime_value,
    NTILE(10) OVER (ORDER BY lifetime_value DESC) as customer_decile,
    CASE 
        WHEN NTILE(10) OVER (ORDER BY lifetime_value DESC) = 1 THEN 'Top 10%'
        WHEN NTILE(10) OVER (ORDER BY lifetime_value DESC) <= 5 THEN 'Top 50%'
        ELSE 'Bottom 50%'
    END as customer_segment
FROM customers;
-- Distribution analysis
SELECT 
    salary_quartile,
    COUNT(*) as employee_count,
    MIN(salary) as min_salary,
    MAX(salary) as max_salary
FROM (
    SELECT 
        salary,
        NTILE(4) OVER (ORDER BY salary) as salary_quartile
    FROM employees
)
GROUP BY salary_quartile
ORDER BY salary_quartile;

Part Three: Aggregate Analytic Functions

3.1 Windowed SUM and Running Totals

Running totals reveal accumulation patterns over time:

-- Simple running total
SELECT 
    order_date,
    order_id,
    order_amount,
    SUM(order_amount) OVER (
        ORDER BY order_date, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total,
    SUM(order_amount) OVER (
        ORDER BY order_date, order_id
    ) as cumulative_to_current
FROM orders
ORDER BY order_date, order_id;

-- Result demonstrates cumulative progression:
/*
Date       Order   Amount   Running_Total   Cumulative_to_Current
2024-01-01 101     100      100             100
2024-01-01 102     150      250             250
2024-01-02 103     200      450             450
2024-01-02 104     75       525             525
*/
-- Running total by partition
SELECT 
    employee_id,
    salary_review_date,
    bonus_amount,
    SUM(bonus_amount) OVER (
        PARTITION BY employee_id
        ORDER BY salary_review_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_bonus,
    SUM(bonus_amount) OVER (
        PARTITION BY employee_id
    ) as total_bonuses_all_time
FROM salary_reviews
ORDER BY employee_id, salary_review_date;
-- Year-to-date running total
SELECT 
    order_date,
    YEAR(order_date) as sales_year,
    MONTH(order_date) as sales_month,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY YEAR(order_date)
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as year_to_date_sales
FROM orders
ORDER BY order_date;
-- Moving average (windowed AVG)
SELECT 
    observation_date,
    temperature,
    AVG(temperature) OVER (
        ORDER BY observation_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_moving_average,
    AVG(temperature) OVER (
        ORDER BY observation_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as thirty_day_moving_average
FROM temperature_readings
ORDER BY observation_date;

3.2 Windowed COUNT and Frequency Analysis

COUNT with OVER clause provides row counts within windows:

-- Count transactions per customer
SELECT 
    customer_id,
    customer_name,
    transaction_date,
    transaction_amount,
    COUNT(*) OVER (PARTITION BY customer_id) as customer_transaction_count,
    COUNT(*) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_transaction_count
FROM transactions
ORDER BY customer_id, transaction_date;

-- Count distinct values in window (SQL Server 2019+)
SELECT 
    employee_id,
    project_id,
    assignment_date,
    COUNT(DISTINCT project_id) OVER (
        PARTITION BY employee_id
        ORDER BY assignment_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_unique_projects
FROM employee_projects
ORDER BY employee_id, assignment_date;
-- Count specific conditions
SELECT 
    order_date,
    order_id,
    order_amount,
    SUM(CASE WHEN order_amount > 1000 THEN 1 ELSE 0 END) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_large_orders
FROM orders
ORDER BY order_date;

3.3 Windowed MIN and MAX

Finding minimum/maximum values within windows:

-- Price range in trading window
SELECT 
    trading_date,
    ticker,
    closing_price,
    MIN(closing_price) OVER (
        PARTITION BY ticker
        ORDER BY trading_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as thirty_day_low,
    MAX(closing_price) OVER (
        PARTITION BY ticker
        ORDER BY trading_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as thirty_day_high,
    closing_price - MIN(closing_price) OVER (
        PARTITION BY ticker
        ORDER BY trading_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) as distance_from_low
FROM stock_prices
ORDER BY ticker, trading_date;

-- Employee salary range by department
SELECT 
    employee_id,
    employee_name,
    department,
    salary,
    MIN(salary) OVER (PARTITION BY department) as dept_min_salary,
    MAX(salary) OVER (PARTITION BY department) as dept_max_salary,
    salary - MIN(salary) OVER (PARTITION BY department) as salary_above_minimum,
    CAST((salary - MIN(salary) OVER (PARTITION BY department)) * 100.0 / 
        (MAX(salary) OVER (PARTITION BY department) - MIN(salary) OVER (PARTITION BY department)) 
        AS DECIMAL(5,2)) as salary_percentile_within_dept
FROM employees;
-- Product performance tracking
SELECT 
    product_id,
    sales_date,
    daily_sales,
    MIN(daily_sales) OVER (
        PARTITION BY product_id
        ORDER BY sales_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as all_time_low_sales,
    MAX(daily_sales) OVER (
        PARTITION BY product_id
        ORDER BY sales_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as all_time_high_sales
FROM product_sales
ORDER BY product_id, sales_date;

Part Four: Offset Analytic Functions

4.1 LAG() and LEAD() Functions

LAG and LEAD access values from previous or following rows:

-- Basic LAG/LEAD usage
SELECT 
    order_date,
    order_id,
    order_amount,
    LAG(order_amount) OVER (ORDER BY order_date, order_id) as previous_order_amount,
    LEAD(order_amount) OVER (ORDER BY order_date, order_id) as next_order_amount,
    order_amount - LAG(order_amount) OVER (ORDER BY order_date, order_id) as change_from_previous,
    LEAD(order_amount) OVER (ORDER BY order_date, order_id) - order_amount as change_to_next
FROM orders
ORDER BY order_date, order_id;

-- Multi-row offset
SELECT 
    trading_date,
    closing_price,
    LAG(closing_price, 1) OVER (ORDER BY trading_date) as previous_day_close,
    LAG(closing_price, 5) OVER (ORDER BY trading_date) as week_ago_close,
    LAG(closing_price, 20) OVER (ORDER BY trading_date) as month_ago_close,
    closing_price - LAG(closing_price, 1) OVER (ORDER BY trading_date) as daily_change,
    CAST((closing_price - LAG(closing_price, 1) OVER (ORDER BY trading_date)) * 100.0 / 
        LAG(closing_price, 1) OVER (ORDER BY trading_date) AS DECIMAL(5,2)) as daily_pct_change
FROM stock_prices
WHERE ticker = 'AAPL'
ORDER BY trading_date;
-- LAG/LEAD with partitioning
SELECT 
    employee_id,
    employee_name,
    salary_review_date,
    salary,
    LAG(salary) OVER (PARTITION BY employee_id ORDER BY salary_review_date) as previous_salary,
    salary - LAG(salary) OVER (PARTITION BY employee_id ORDER BY salary_review_date) as salary_increase,
    LEAD(salary) OVER (PARTITION BY employee_id ORDER BY salary_review_date) as next_salary
FROM salary_history
ORDER BY employee_id, salary_review_date;
-- Default values for NULL results
SELECT 
    order_date,
    order_id,
    order_amount,
    COALESCE(LAG(order_amount) OVER (ORDER BY order_date, order_id), order_amount) as previous_or_current,
    LAG(order_amount, 1, 0) OVER (ORDER BY order_date, order_id) as previous_with_zero_default
FROM orders
ORDER BY order_date, order_id;
-- Identify gaps in sequences
SELECT 
    order_date,
    order_id,
    order_amount,
    LAG(order_date) OVER (ORDER BY order_date) as previous_order_date,
    DATEDIFF(DAY, LAG(order_date) OVER (ORDER BY order_date), order_date) as days_since_previous_order,
    CASE 
        WHEN DATEDIFF(DAY, LAG(order_date) OVER (ORDER BY order_date), order_date) > 7 THEN 'Large Gap'
        WHEN DATEDIFF(DAY, LAG(order_date) OVER (ORDER BY order_date), order_date) IS NULL THEN 'First Order'
        ELSE 'Regular Pattern'
    END as order_pattern
FROM orders
ORDER BY order_date;

4.2 FIRST_VALUE() and LAST_VALUE()

Extract first or last values within windows:

-- First and last transaction per customer
SELECT 
    customer_id,
    customer_name,
    transaction_date,
    transaction_amount,
    FIRST_VALUE(transaction_date) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
    ) as first_transaction_date,
    LAST_VALUE(transaction_date) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_transaction_date,
    FIRST_VALUE(transaction_amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
    ) as first_transaction_amount,
    LAST_VALUE(transaction_amount) OVER (
        PARTITION BY customer_id
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as last_transaction_amount
FROM transactions
ORDER BY customer_id, transaction_date;

-- Compare current to first/last
SELECT 
    employee_id,
    salary_review_date,
    salary,
    FIRST_VALUE(salary) OVER (
        PARTITION BY employee_id
        ORDER BY salary_review_date
    ) as starting_salary,
    LAST_VALUE(salary) OVER (
        PARTITION BY employee_id
        ORDER BY salary_review_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as current_salary,
    salary - FIRST_VALUE(salary) OVER (
        PARTITION BY employee_id
        ORDER BY salary_review_date
    ) as absolute_increase_from_start,
    CAST((salary - FIRST_VALUE(salary) OVER (
        PARTITION BY employee_id
        ORDER BY salary_review_date
    )) * 100.0 / FIRST_VALUE(salary) OVER (
        PARTITION BY employee_id
        ORDER BY salary_review_date
    ) AS DECIMAL(5,2)) as pct_increase_from_start
FROM salary_history
ORDER BY employee_id, salary_review_date;
-- First/last within moving window
SELECT 
    observation_date,
    temperature,
    FIRST_VALUE(temperature) OVER (
        ORDER BY observation_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as week_start_temperature,
    LAST_VALUE(temperature) OVER (
        ORDER BY observation_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as week_end_temperature,
    LAST_VALUE(temperature) OVER (
        ORDER BY observation_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) - FIRST_VALUE(temperature) OVER (
        ORDER BY observation_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as weekly_temperature_change
FROM temperature_readings
ORDER BY observation_date;

Part Five: Distribution and Statistical Functions

5.1 PERCENT_RANK() and CUME_DIST()

These functions provide percentile-based ranking:

-- Percent rank demonstrates progression through dataset
SELECT 
    employee_id,
    employee_name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) as salary_percent_rank,
    CAST(PERCENT_RANK() OVER (ORDER BY salary) * 100 AS DECIMAL(5,2)) as salary_percentile,
    CUME_DIST() OVER (ORDER BY salary) as salary_cume_dist,
    CAST(CUME_DIST() OVER (ORDER BY salary) * 100 AS DECIMAL(5,2)) as salary_cumulative_pct
FROM employees
ORDER BY salary;

-- Comparison of ranking methods:
/*
PERCENT_RANK() = (RANK - 1) / (Total Rows - 1)
CUME_DIST() = (Count of rows <= current) / (Total Rows)
Result: Different values despite measuring similar concepts
PERCENT_RANK(): 0.0 to 1.0, gaps for ties
CUME_DIST(): 0.0 to 1.0, no gaps between distinct values
*/
-- Identify quartile positions
SELECT 
    customer_id,
    customer_name,
    lifetime_value,
    PERCENT_RANK() OVER (ORDER BY lifetime_value DESC) as ltv_percent_rank,
    CASE 
        WHEN PERCENT_RANK() OVER (ORDER BY lifetime_value DESC) <= 0.25 THEN 'Top Quartile'
        WHEN PERCENT_RANK() OVER (ORDER BY lifetime_value DESC) <= 0.50 THEN 'Second Quartile'
        WHEN PERCENT_RANK() OVER (ORDER BY lifetime_value DESC) <= 0.75 THEN 'Third Quartile'
        ELSE 'Bottom Quartile'
    END as ltv_quartile
FROM customers;
-- Performance against peers
SELECT 
    employee_id,
    employee_name,
    department,
    annual_sales,
    PERCENT_RANK() OVER (PARTITION BY department ORDER BY annual_sales DESC) as dept_percent_rank,
    CAST(PERCENT_RANK() OVER (PARTITION BY department ORDER BY annual_sales DESC) * 100 AS DECIMAL(5,2)) as dept_percentile,
    CUME_DIST() OVER (PARTITION BY department ORDER BY annual_sales DESC) as dept_cume_dist,
    COUNT(*) OVER (PARTITION BY department) as dept_employee_count
FROM sales_employees;

5.2 Statistical Analytic Functions

Calculate statistical measures within windows:

-- Standard deviation and variance within windows
SELECT 
    product_category,
    product_id,
    product_price,
    AVG(product_price) OVER (PARTITION BY product_category) as category_avg_price,
    STDEV(product_price) OVER (PARTITION BY product_category) as category_stdev,
    product_price - AVG(product_price) OVER (PARTITION BY product_category) as price_vs_category_avg,
    CAST((product_price - AVG(product_price) OVER (PARTITION BY product_category)) / 
        NULLIF(STDEV(product_price) OVER (PARTITION BY product_category), 0) 
        AS DECIMAL(5,2)) as standard_deviations_from_mean
FROM products;

-- Identify outliers
WITH price_statistics AS (
    SELECT 
        product_id,
        product_price,
        AVG(product_price) OVER () as overall_avg_price,
        STDEV(product_price) OVER () as overall_stdev,
        product_price - AVG(product_price) OVER () as price_deviation
    FROM products
)
SELECT 
    product_id,
    product_price,
    overall_avg_price,
    overall_stdev,
    price_deviation,
    CASE 
        WHEN ABS(price_deviation) > 2 * overall_stdev THEN 'Outlier (>2 SD)'
        WHEN ABS(price_deviation) > 1 * overall_stdev THEN 'Unusual (>1 SD)'
        ELSE 'Normal'
    END as price_classification
FROM price_statistics
WHERE ABS(price_deviation) > overall_stdev;
-- Variance analysis
SELECT 
    sales_date,
    daily_sales,
    AVG(daily_sales) OVER (ORDER BY sales_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as monthly_avg,
    VAR_SAMP(daily_sales) OVER (ORDER BY sales_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) as monthly_variance,
    SQRT(VAR_SAMP(daily_sales) OVER (ORDER BY sales_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)) as monthly_stdev
FROM daily_sales
ORDER BY sales_date;

Part Six: Advanced Analytic Patterns

6.1 Gap and Island Detection

Identifying consecutive sequences (islands) and gaps:

-- Detect service outages (gaps in monitoring data)
WITH outage_groups AS (
    SELECT 
        monitor_id,
        check_timestamp,
        status,
        ROW_NUMBER() OVER (PARTITION BY monitor_id ORDER BY check_timestamp) as rn,
        ROW_NUMBER() OVER (PARTITION BY monitor_id ORDER BY check_timestamp) -
            ROW_NUMBER() OVER (PARTITION BY monitor_id, status ORDER BY check_timestamp) as island_group
    FROM system_monitoring
)
SELECT 
    monitor_id,
    status,
    MIN(check_timestamp) as island_start,
    MAX(check_timestamp) as island_end,
    DATEDIFF(HOUR, MIN(check_timestamp), MAX(check_timestamp)) as duration_hours,
    COUNT(*) as check_count
FROM outage_groups
WHERE status = 'DOWN'
GROUP BY monitor_id, status, island_group
ORDER BY monitor_id, island_start;

-- Consecutive product purchases
WITH purchase_sequences AS (
    SELECT 
        customer_id,
        product_id,
        purchase_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) as purchase_order,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date) -
            ROW_NUMBER() OVER (PARTITION BY customer_id, product_id ORDER BY purchase_date) as sequence_group
    FROM customer_purchases
)
SELECT 
    customer_id,
    product_id,
    MIN(purchase_date) as sequence_start,
    MAX(purchase_date) as sequence_end,
    COUNT(*) as consecutive_purchases
FROM purchase_sequences
GROUP BY customer_id, product_id, sequence_group
HAVING COUNT(*) >= 2
ORDER BY customer_id, consecutive_purchases DESC;

6.2 Percent of Total Calculations

Calculate row values as percentage of total:

-- Sales as percent of regional total
SELECT 
    region,
    product_id,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY region) as regional_total,
    CAST(sales_amount * 100.0 / SUM(sales_amount) OVER (PARTITION BY region) AS DECIMAL(5,2)) as pct_of_region_sales,
    SUM(sales_amount) OVER () as company_total,
    CAST(sales_amount * 100.0 / SUM(sales_amount) OVER () AS DECIMAL(5,2)) as pct_of_company_sales
FROM regional_sales
ORDER BY region, sales_amount DESC;

-- Marketing spend allocation
SELECT 
    campaign_id,
    campaign_name,
    channel,
    spend_amount,
    SUM(spend_amount) OVER (PARTITION BY campaign_id) as campaign_budget,
    CAST(spend_amount * 100.0 / SUM(spend_amount) OVER (PARTITION BY campaign_id) AS DECIMAL(5,2)) as pct_of_campaign,
    SUM(spend_amount) OVER () as total_marketing_budget,
    CAST(spend_amount * 100.0 / SUM(spend_amount) OVER () AS DECIMAL(5,2)) as pct_of_total_budget
FROM marketing_campaigns
ORDER BY campaign_id, spend_amount DESC;
-- Revenue contribution by customer segment
SELECT 
    customer_segment,
    customer_id,
    annual_revenue,
    SUM(annual_revenue) OVER (PARTITION BY customer_segment) as segment_revenue,
    CAST(annual_revenue * 100.0 / SUM(annual_revenue) OVER (PARTITION BY customer_segment) AS DECIMAL(7,2)) as pct_of_segment,
    SUM(annual_revenue) OVER () as total_revenue,
    CAST(annual_revenue * 100.0 / SUM(annual_revenue) OVER () AS DECIMAL(7,2)) as pct_of_total,
    ROUND(annual_revenue * 100.0 / SUM(annual_revenue) OVER (PARTITION BY customer_segment) / 
        (SELECT COUNT(*) FROM customers WHERE customer_segment = c.customer_segment), 2) as avg_customer_contribution
FROM customers c
ORDER BY customer_segment, annual_revenue DESC;

6.3 Cumulative Percentage Distribution

Track progressive accumulation:

-- Cumulative percentage (Pareto analysis)
WITH sales_ranking AS (
    SELECT 
        product_id,
        product_name,
        sales_amount,
        SUM(sales_amount) OVER () as total_sales,
        ROW_NUMBER() OVER (ORDER BY sales_amount DESC) as sales_rank
    FROM product_sales
)
SELECT 
    sales_rank,
    product_id,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sales_rank) as cumulative_sales,
    CAST(SUM(sales_amount) OVER (ORDER BY sales_rank) * 100.0 / total_sales AS DECIMAL(5,2)) as cumulative_pct,
    CASE 
        WHEN SUM(sales_amount) OVER (ORDER BY sales_rank) * 100.0 / total_sales <= 80 THEN '80/20 Core'
        ELSE 'Long Tail'
    END as category
FROM sales_ranking
ORDER BY sales_rank;

-- Customer value accumulation
SELECT 
    customer_rank,
    customer_id,
    customer_name,
    annual_revenue,
    SUM(annual_revenue) OVER (ORDER BY annual_revenue DESC) as cumulative_revenue,
    CAST(SUM(annual_revenue) OVER (ORDER BY annual_revenue DESC) * 100.0 / 
        SUM(annual_revenue) OVER () AS DECIMAL(5,2)) as revenue_cumulative_pct
FROM (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY annual_revenue DESC) as customer_rank,
        customer_id,
        customer_name,
        annual_revenue
    FROM customers
) ranked_customers
ORDER BY customer_rank;

Part Seven: PySpark Analytic Function Implementation

7.1 PySpark Window Specifications

PySpark provides equivalent analytic capabilities through window functions:

from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import (
    col, row_number, rank, dense_rank, ntile,
    sum as spark_sum, count, avg, min as spark_min, max as spark_max,
    lag, lead, first, last,
    percent_rank, cume_dist,
    stddev, variance
)

spark = SparkSession.builder \
    .appName("AnalyticFunctionsDemo") \
    .getOrCreate()
# Sample data
employees_data = [
    ("E001", "Alice", "Sales", 95000),
    ("E002", "Bob", "Engineering", 92000),
    ("E003", "Charlie", "Sales", 92000),
    ("E004", "David", "Finance", 88000),
    ("E005", "Eve", "Sales", 85000),
]
df_employees = spark.createDataFrame(
    employees_data,
    ["emp_id", "emp_name", "department", "salary"]
)
# Basic window specifications
basic_window = Window.orderBy("salary")
dept_window = Window.partitionBy("department").orderBy("salary")
dept_window_full = Window.partitionBy("department") \
    .orderBy("salary") \
    .rangeBetween(Window.unboundedPreceding, Window.unboundedFollowing)
# Ranking functions
ranking_df = df_employees.select(
    col("emp_id"),
    col("emp_name"),
    col("department"),
    col("salary"),
    row_number().over(basic_window).alias("row_num"),
    rank().over(basic_window).alias("rank_value"),
    dense_rank().over(basic_window).alias("dense_rank_value"),
    ntile(4).over(basic_window).alias("quartile"),
    percent_rank().over(basic_window).alias("percent_rank_value"),
    cume_dist().over(basic_window).alias("cume_dist_value")
)
ranking_df.show(truncate=False)
# Aggregate functions with windows
agg_df = df_employees.select(
    col("emp_id"),
    col("emp_name"),
    col("department"),
    col("salary"),
    avg("salary").over(dept_window).alias("dept_avg_salary"),
    spark_min("salary").over(dept_window).alias("dept_min_salary"),
    spark_max("salary").over(dept_window).alias("dept_max_salary"),
    spark_sum("salary").over(dept_window_full).alias("dept_total_salary")
)
agg_df.show(truncate=False)
# Offset functions
offset_df = df_employees.select(
    col("emp_name"),
    col("salary"),
    lag("salary").over(basic_window).alias("previous_salary"),
    lead("salary").over(basic_window).alias("next_salary"),
    first("salary").over(dept_window).alias("dept_first_salary"),
    last("salary").over(dept_window_full).alias("dept_last_salary")
)
offset_df.show(truncate=False)

7.2 Advanced PySpark Analytic Patterns

from pyspark.sql import SparkSession, Window
from pyspark.sql.functions import (
    col, row_number, rank, dense_rank,
    sum as spark_sum, avg, max as spark_max, min as spark_min,
    lag, lead, when, case,
    datediff, date_format, to_date,
    cast, round as spark_round
)
from pyspark.sql.types import IntegerType, DecimalType

spark = SparkSession.builder \
    .appName("AdvancedAnalyticPatterns") \
    .getOrCreate()
# Pattern 1: Running totals and moving averages
orders_data = [
    ("2024-01-01", 100),
    ("2024-01-02", 150),
    ("2024-01-03", 120),
    ("2024-01-04", 200),
    ("2024-01-05", 180),
]
df_orders = spark.createDataFrame(
    orders_data,
    ["order_date", "order_amount"]
)
# Define windows
cumulative_window = Window.orderBy("order_date") \
    .rangeBetween(Window.unboundedPreceding, Window.currentRow)
moving_window = Window.orderBy("order_date") \
    .rangeBetween(-2, Window.currentRow)  # 3-day window
running_totals = df_orders.select(
    col("order_date"),
    col("order_amount"),
    spark_sum("order_amount").over(cumulative_window).alias("cumulative_total"),
    spark_round(avg("order_amount").over(moving_window), 2).alias("three_day_moving_avg")
)
running_totals.show(truncate=False)
# Pattern 2: Gap and island detection
outage_data = [
    ("Monitor-1", "2024-01-01 08:00", "UP"),
    ("Monitor-1", "2024-01-01 09:00", "UP"),
    ("Monitor-1", "2024-01-01 10:00", "DOWN"),
    ("Monitor-1", "2024-01-01 11:00", "DOWN"),
    ("Monitor-1", "2024-01-01 12:00", "DOWN"),
    ("Monitor-1", "2024-01-01 13:00", "UP"),
    ("Monitor-1", "2024-01-01 14:00", "UP"),
]
df_outages = spark.createDataFrame(
    outage_data,
    ["monitor_id", "check_time", "status"]
)
window_rn = Window.partitionBy("monitor_id").orderBy("check_time")
window_status_rn = Window.partitionBy("monitor_id", "status").orderBy("check_time")
outage_islands = df_outages.select(
    col("monitor_id"),
    col("check_time"),
    col("status"),
    (row_number().over(window_rn) - 
     row_number().over(window_status_rn)).alias("island_group")
).groupBy("monitor_id", "status", "island_group").agg(
    spark_min("check_time").alias("outage_start"),
    spark_max("check_time").alias("outage_end")
).filter(col("status") == "DOWN")
outage_islands.show(truncate=False)
# Pattern 3: Percent of total calculations
sales_data = [
    ("North", "Product A", 10000),
    ("North", "Product B", 8000),
    ("South", "Product A", 12000),
    ("South", "Product B", 9000),
]
df_sales = spark.createDataFrame(
    sales_data,
    ["region", "product", "sales"]
)
window_regional = Window.partitionBy("region")
window_company = Window.orderBy()
pct_of_total = df_sales.select(
    col("region"),
    col("product"),
    col("sales"),
    spark_sum("sales").over(window_regional).alias("regional_total"),
    spark_sum("sales").over(window_company).alias("company_total"),
    spark_round(
        col("sales") * 100.0 / spark_sum("sales").over(window_regional), 2
    ).alias("pct_of_region"),
    spark_round(
        col("sales") * 100.0 / spark_sum("sales").over(window_company), 2
    ).alias("pct_of_company")
)
pct_of_total.show(truncate=False)
# Pattern 4: Ranking with partitioning
employees_data = [
    ("E001", "Alice", "Sales", 95000),
    ("E002", "Bob", "Engineering", 92000),
    ("E003", "Charlie", "Sales", 92000),
    ("E004", "David", "Finance", 88000),
    ("E005", "Eve", "Sales", 85000),
]
df_employees = spark.createDataFrame(
    employees_data,
    ["emp_id", "emp_name", "department", "salary"]
)
window_dept = Window.partitionBy("department").orderBy(col("salary").desc())
window_company = Window.orderBy(col("salary").desc())
ranked_employees = df_employees.select(
    col("emp_id"),
    col("emp_name"),
    col("department"),
    col("salary"),
    rank().over(window_dept).alias("dept_rank"),
    rank().over(window_company).alias("company_rank"),
    percent_rank().over(window_dept).alias("dept_percentile"),
    when(
        rank().over(window_dept) <= 2, "Top Performer"
    ).otherwise("Standard").alias("dept_status")
)
ranked_employees.show(truncate=False)

Part Eight: Performance Optimization

8.1 Execution Plan Analysis

Understanding analytic function execution optimizes performance:

-- Check execution plan for analytic query
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT 
    order_date,
    order_id,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY order_date
        ORDER BY order_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY order_date, order_id;
-- Execution plan analysis notes:
/*
Key considerations:
1. Sort operations: ORDER BY clause in OVER requires sort
2. Window spool: Accumulates rows for window calculation
3. Parallelism: Analytic functions may reduce parallelization
4. Memory usage: Large windows consume significant memory
5. Cardinality: Partitioning reduces window size (beneficial)
*/
-- Optimize with proper indexing
CREATE NONCLUSTERED INDEX ix_orders_date_id
    ON orders(order_date, order_id)
    INCLUDE (order_amount);
-- Now analytic query uses index more efficiently

8.2 Partitioning Strategy

Smart partitioning dramatically improves analytic performance:

-- Large window (poor performance)
SELECT 
    order_id,
    order_amount,
    SUM(order_amount) OVER () as grand_total  -- No partition, entire dataset
FROM orders;
-- Result: All 10 million rows processed in single window

-- Partitioned window (better performance)
SELECT 
    order_date,
    order_id,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY YEAR(order_date), MONTH(order_date)) as monthly_total
FROM orders;
-- Result: 12 independent windows (one per month), each processes fewer rows
-- Multiple independent windows (optimal)
SELECT 
    region,
    order_date,
    order_id,
    order_amount,
    SUM(order_amount) OVER (PARTITION BY region) as regional_total,
    SUM(order_amount) OVER (PARTITION BY region, YEAR(order_date)) as regional_yearly_total,
    SUM(order_amount) OVER (PARTITION BY region, YEAR(order_date), MONTH(order_date)) as regional_monthly_total
FROM orders;
-- Result: Multiple small windows, parallel processing possible

Part Nine: Real-World Analytical Scenarios

9.1 Customer Behavior Analysis

Comprehensive customer analytics using multiple analytic functions:

-- Customer purchase behavior analysis
WITH customer_activity AS (
    SELECT 
        c.customer_id,
        c.customer_name,
        c.region,
        o.order_date,
        o.order_amount,
        COUNT(*) OVER (PARTITION BY c.customer_id) as lifetime_order_count,
        SUM(o.order_amount) OVER (PARTITION BY c.customer_id) as lifetime_value,
        LAG(o.order_date) OVER (PARTITION BY c.customer_id ORDER BY o.order_date) as previous_order_date,
        DATEDIFF(DAY, LAG(o.order_date) OVER (PARTITION BY c.customer_id ORDER BY o.order_date), o.order_date) as days_since_previous_order,
        ROW_NUMBER() OVER (PARTITION BY c.customer_id ORDER BY o.order_date DESC) as order_recency_rank,
        PERCENT_RANK() OVER (PARTITION BY c.region ORDER BY o.order_amount DESC) as regional_spend_percentile
    FROM dbo.customers c
    INNER JOIN dbo.orders o ON c.customer_id = o.customer_id
)
SELECT 
    customer_id,
    customer_name,
    region,
    lifetime_order_count,
    lifetime_value,
    CAST(lifetime_value / lifetime_order_count AS DECIMAL(10,2)) as avg_order_value,
    CASE 
        WHEN regional_spend_percentile <= 0.1 THEN 'Top 10% Regional Spender'
        WHEN regional_spend_percentile <= 0.25 THEN 'Top 25% Regional Spender'
        WHEN regional_spend_percentile <= 0.5 THEN 'Above Average Regional Spender'
        ELSE 'Standard Regional Spender'
    END as customer_segment,
    CASE 
        WHEN order_recency_rank = 1 AND days_since_previous_order > 90 THEN 'At Risk (Long Time Since Order)'
        WHEN order_recency_rank = 1 AND days_since_previous_order IS NULL THEN 'First Time Ever'
        WHEN order_recency_rank = 1 AND days_since_previous_order <= 30 THEN 'Recent Active'
        WHEN lifetime_order_count >= 50 AND order_recency_rank <= 10 THEN 'VIP High Frequency'
        ELSE 'Regular Customer'
    END as customer_status
FROM customer_activity
WHERE order_recency_rank = 1;  -- Most recent order only

9.2 Financial Performance Benchmarking

Year-over-year and peer comparison analysis:

-- Financial performance comparison
SELECT 
    sales_date,
    YEAR(sales_date) as fiscal_year,
    MONTH(sales_date) as fiscal_month,
    region,
    product_category,
    daily_sales,
    LAG(daily_sales) OVER (
        PARTITION BY region, product_category, MONTH(sales_date)
        ORDER BY YEAR(sales_date)
    ) as previous_year_same_month,
    daily_sales - LAG(daily_sales) OVER (
        PARTITION BY region, product_category, MONTH(sales_date)
        ORDER BY YEAR(sales_date)
    ) as yoy_change,
    CAST((daily_sales - LAG(daily_sales) OVER (
        PARTITION BY region, product_category, MONTH(sales_date)
        ORDER BY YEAR(sales_date)
    )) * 100.0 / LAG(daily_sales) OVER (
        PARTITION BY region, product_category, MONTH(sales_date)
        ORDER BY YEAR(sales_date)
    ) AS DECIMAL(5,2)) as yoy_pct_change,
    AVG(daily_sales) OVER (
        PARTITION BY region, product_category, YEAR(sales_date)
    ) as yearly_daily_avg,
    daily_sales - AVG(daily_sales) OVER (
        PARTITION BY region, product_category, YEAR(sales_date)
    ) as variance_from_yearly_avg,
    PERCENT_RANK() OVER (
        PARTITION BY YEAR(sales_date), MONTH(sales_date)
        ORDER BY daily_sales DESC
    ) as monthly_performance_percentile
FROM daily_regional_sales
WHERE sales_date >= '2023-01-01'
ORDER BY region, product_category, sales_date;

Conclusion

Analytic functions represent perhaps the most powerful SQL capability for sophisticated data analysis. Unlike aggregate functions that collapse rows, analytic functions preserve row-level detail while providing windowed calculations across subsets of data. This duality enables complex analytical patterns including running totals, ranking, distribution analysis, and temporal comparisons within single, efficient SQL queries.

Mastery of analytic function syntax, window frame specifications, and optimization strategies differentiates you as a data professional capable of addressing complex analytical requirements with elegant, performant SQL implementations.

These functions transcend database platforms and remain universally applicable regardless of technological infrastructure, enabling seamless translation of analytical skills across organizational boundaries and geographic regions.

메타데이터

post_id: 23bc2302fd8e
slug: day-17-of-32-days-of-sql-concepts-analytic-functions-23bc2302fd8e
url: https://medium.com/@krthiak/day-17-of-32-days-of-sql-concepts-analytic-functions-23bc2302fd8e
canonical_url: https://medium.com/@krthiak/day-17-of-32-days-of-sql-concepts-analytic-functions-23bc2302fd8e
author_url: https://medium.com/@krthiak
status: ok
fetched_at: 2026-06-14 16:17:09

Day 17 of 32 Days of SQL Concepts — Analytic Functions

Part One: Foundational Analytic Function Concepts

1.1 Analytic Functions versus Aggregate Functions

1.2 OVER Clause: The Analytic Function Specification

1.3 Analytic Function Categories

Part Two: Ranking Analytic Functions

2.1 ROW_NUMBER() Function

2.2 RANK() and DENSE_RANK() Functions

2.3 NTILE(n) Function

Part Three: Aggregate Analytic Functions

3.1 Windowed SUM and Running Totals

3.2 Windowed COUNT and Frequency Analysis

3.3 Windowed MIN and MAX

Part Four: Offset Analytic Functions

4.1 LAG() and LEAD() Functions

4.2 FIRST_VALUE() and LAST_VALUE()

Part Five: Distribution and Statistical Functions

5.1 PERCENT_RANK() and CUME_DIST()

5.2 Statistical Analytic Functions

Part Six: Advanced Analytic Patterns

6.1 Gap and Island Detection

6.2 Percent of Total Calculations

6.3 Cumulative Percentage Distribution

Part Seven: PySpark Analytic Function Implementation

7.1 PySpark Window Specifications

7.2 Advanced PySpark Analytic Patterns

Part Eight: Performance Optimization

8.1 Execution Plan Analysis

8.2 Partitioning Strategy

Part Nine: Real-World Analytical Scenarios

9.1 Customer Behavior Analysis

9.2 Financial Performance Benchmarking

Conclusion

More from Karthik

Day 16 of 32 Days of SQL Concepts — Materialized Views

Skewed Joins in PySpark: The Silent Killer of Distributed Performance (And How t…

PySpark Internals — Day 87 of 100 Days of Data Engineering, AI and Azure Challen…

More on 🔧 · Data Engineering

The End of Dashboards and Design Systems

Stop Wasting Your Money — 10 Expenses That No Longer Make Sense

7 Everyday Phrases We Use That Were Designed to Keep People in Their Place

The Obvious Ways To Spot Someone Secretly Writing With AI

Why Niche Contextual Forum Backlinks Still Work in 2026