Advanced Analytics Functions: Examples

Employee Performance Analysis with Statistical Functions

intermediate

Analyze employee performance using statistical measures to identify outliers and benchmarks

-- Comprehensive employee performance analysis

WITH performance_stats AS (

SELECT

employee_id,

name,

department,

salary,

performance_score,

years_experience,

-- Statistical measures

AVG(salary) OVER () as company_avg_salary,

STDDEV(salary) OVER () as company_salary_stddev,

-- Department-level statistics

AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,

STDDEV(salary) OVER (PARTITION BY department) as dept_salary_stddev,

-- Position in distribution

PERCENT_RANK() OVER (ORDER BY salary) as salary_percentile,

PERCENT_RANK() OVER (ORDER BY performance_score) as performance_percentile,

-- Cumulative distribution

CUME_DIST() OVER (ORDER BY salary) as salary_cumulative_dist

FROM employees

WHERE active = true

),

analysis_results AS (

SELECT

*,

-- Identify statistical outliers (beyond 2 standard deviations)

CASE

WHEN ABS(salary - company_avg_salary) > 2 * company_salary_stddev THEN 'Salary Outlier'

WHEN ABS(salary - dept_avg_salary) > 2 * dept_salary_stddev THEN 'Dept Salary Outlier'

ELSE 'Normal Range'

END as salary_category,

-- Performance tiers based on percentiles

CASE

WHEN performance_percentile >= 0.9 THEN 'Top 10% Performer'

WHEN performance_percentile >= 0.75 THEN 'Top 25% Performer'

WHEN performance_percentile >= 0.5 THEN 'Above Average'