Advanced Analytic Functions

Advanced SQL

Statistical Power in SQL

SQL's analytic functions go far beyond simple aggregates — they compute linear regression, correlation, percentiles, moving statistics, and outlier detection directly in the database. No R or Python needed for quick statistical analysis.

Linear Regression — REGR_SLOPE, REGR_INTERCEPT, REGR_R2
Correlation — CORR() returns Pearson's r (-1 to 1)
Percentiles — PERCENTILE_CONT (continuous) vs PERCENTILE_DISC (discrete)
Outlier Detection — Z-score method using AVG + STDDEV

These functions are essential for data science interviews — they test whether you can do statistical analysis without leaving SQL.

Statistical Functions Reference

Linear Regression Formula

Given data points , the least-squares regression line is:

Statistical Aggregate Functions

-- Advanced statistical calculations per department
SELECT
  department_id,
  COUNT(*) AS n,
  AVG(salary) AS mean,
  STDDEV_POP(salary) AS std_dev_population,
  STDDEV_SAMP(salary) AS std_dev_sample,
  VAR_POP(salary) AS variance_population,
  VAR_SAMP(salary) AS variance_sample,
  CORR(salary, years_experience) AS correlation,
  REGR_SLOPE(salary, years_experience) AS regression_slope,
  REGR_INTERCEPT(salary, years_experience) AS regression_intercept,
  REGR_R2(salary, years_experience) AS r_squared
FROM employees
GROUP BY department_id;

ℹ️

Key Insight: Use _POP for population statistics and _SAMP for sample statistics. CORR() returns the Pearson correlation coefficient (-1 to 1). Values near 0 indicate no linear relationship.

Linear Regression Analysis

-- Predict salary based on years of experience per department
WITH regression_stats AS (
  SELECT
    department_id,
    REGR_SLOPE(salary, years_experience) AS slope,
    REGR_INTERCEPT(salary, years_experience) AS intercept,
    REGR_R2(salary, years_experience) AS r_squared
  FROM employees
  GROUP BY department_id
)
SELECT
  e.employee_id,
  e.department_id,
  e.salary,
  e.years_experience,
  r.slope * e.years_experience + r.intercept AS predicted_salary,
  e.salary - (r.slope * e.years_experience + r.intercept) AS residual,
  r.r_squared
FROM employees e
INNER JOIN regression_stats r ON e.department_id = r.department_id;

Percentile and Median Calculations

-- Compute percentiles with different interpolation methods
SELECT
  department_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_continuous,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) AS median_discrete,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) AS q3,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY salary) AS p95,
  PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY salary) AS p99
FROM employees
GROUP BY department_id;

⚠️

CONT vs DISC: PERCENTILE_CONT interpolates between values (returns a decimal), while PERCENTILE_DISC returns an actual value from the dataset. Use CONT for smooth estimates, DISC when you need an existing value.

Moving Aggregations

-- Complex moving window calculations
SELECT
  sale_date, revenue,
  AVG(revenue) OVER w7 AS ma_7day,
  SUM(revenue) OVER w30 AS sum_30day,
  STDDEV(revenue) OVER w7 AS stddev_7day,
  CORR(revenue, units_sold) OVER w7 AS corr_7day,
  REGR_SLOPE(revenue, units_sold) OVER w7 AS slope_7day
FROM daily_sales
WINDOW
  w7 AS (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW),
  w30 AS (ORDER BY sale_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW);

Ratio Calculations

-- Compute various ratios using window functions
SELECT
  department_id, employee_id, salary,
  salary / AVG(salary) OVER (PARTITION BY department_id) AS ratio_to_avg,
  salary / MAX(salary) OVER (PARTITION BY department_id) AS ratio_to_max,
  salary / SUM(salary) OVER (PARTITION BY department_id) * 100 AS pct_of_dept_total,
  salary / SUM(salary) OVER (
    PARTITION BY department_id ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) * 100 AS cumulative_pct
FROM employees;

Statistical Outlier Detection (Z-Score)

-- Identify salary outliers using Z-score
WITH stats AS (
  SELECT
    department_id,
    AVG(salary) AS mean_salary,
    STDDEV(salary) AS stddev_salary
  FROM employees
  GROUP BY department_id
)
SELECT
  e.employee_id, e.department_id, e.salary,
  (e.salary - s.mean_salary) / NULLIF(s.stddev_salary, 0) AS z_score,
  CASE
    WHEN ABS((e.salary - s.mean_salary) / NULLIF(s.stddev_salary, 0)) > 3 THEN 'OUTLIER'
    WHEN ABS((e.salary - s.mean_salary) / NULLIF(s.stddev_salary, 0)) > 2 THEN 'FAR'
    ELSE 'NORMAL'
  END AS classification
FROM employees e
INNER JOIN stats s ON e.department_id = s.department_id;

Z-Score Classification

Z-Score	Classification	Interpretation
\|z\| ≤ 1	Normal	Within 1σ of mean
1 < \|z\| ≤ 2	Moderate	68-95% of data
2 < \|z\| ≤ 3	Far	95-99.7% of data
\|z\| > 3	Outlier	Beyond 99.7%

Frequency Distribution

-- Create salary frequency distribution
SELECT
  WIDTH_BUCKET(salary, 30000, 200000, 10) AS salary_bucket,
  COUNT(*) AS frequency,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct,
  SUM(COUNT(*)) OVER (
    ORDER BY WIDTH_BUCKET(salary, 30000, 200000, 10)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_freq
FROM employees
GROUP BY WIDTH_BUCKET(salary, 30000, 200000, 10)
ORDER BY salary_bucket;

Window Function with FILTER

-- Conditional analytic calculations
SELECT
  department_id, employee_id, salary,
  AVG(salary) FILTER (WHERE status = 'active')
    OVER (PARTITION BY department_id) AS avg_active_salary,
  PERCENT_RANK() OVER (
    PARTITION BY department_id ORDER BY salary
  ) AS percent_rank,
  COUNT(*) FILTER (WHERE performance_rating >= 4)
    OVER (PARTITION BY department_id) AS high_performer_count
FROM employees;

Covariance and Correlation Matrix

-- Compute correlation matrix for multiple variables
SELECT 'salary' AS var1, 'years_experience' AS var2,
  CORR(salary, years_experience) AS correlation
FROM employees
UNION ALL
SELECT 'salary', 'performance_rating',
  CORR(salary, performance_rating) FROM employees
UNION ALL
SELECT 'years_experience', 'performance_rating',
  CORR(years_experience, performance_rating) FROM employees;

Quiz: Test Your Knowledge

Follow-Up Questions

What's the difference between PERCENTILE_CONT and PERCENTILE_DISC?
How would you compute a weighted moving average using window functions?
Explain the assumptions behind REGR_SLOPE and when they might be violated.
How do you handle NULL values in statistical aggregate functions?
What's the best approach for computing rolling percentiles?

Key Takeaways

REGR_SLOPE(y, x) and REGR_INTERCEPT(y, x) compute least-squares regression
CORR(x, y) returns Pearson's r — values near ±1 indicate strong linear relationships
Z-score outlier detection — values beyond ±3σ are statistical outliers
PERCENTILE_CONT interpolates (smooth), PERCENTILE_DISC returns actual values
WIDTH_BUCKET creates equal-width histogram bins for frequency distributions
FILTER clause allows conditional aggregates within window functions

Advanced Analytic Functions — Regression, Statistics, Percentiles & Outlier Detection