sql window-functions analytics aggregation postgresql

Window functions for advanced analytics

Maria Garcia Feb 2026

2 tabs

-- ROW_NUMBER: Unique sequential number
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as overall_rank,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- RANK: Gaps after ties
SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) as rank,
  DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM test_scores;
-- If two people tie for #1, next is #3 with RANK, #2 with DENSE_RANK

-- Running total
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Moving average (last 7 days)
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as moving_avg_7day
FROM daily_revenue;

-- LAG/LEAD: Access previous/next rows
SELECT
  date,
  price,
  LAG(price) OVER (ORDER BY date) as prev_price,
  price - LAG(price) OVER (ORDER BY date) as price_change,
  LEAD(price) OVER (ORDER BY date) as next_price
FROM stock_prices;

-- Percentage of total
SELECT
  product,
  sales,
  sales * 100.0 / SUM(sales) OVER () as pct_of_total,
  sales * 100.0 / SUM(sales) OVER (PARTITION BY category) as pct_of_category
FROM product_sales;

-- FIRST_VALUE/LAST_VALUE
SELECT
  employee_name,
  department,
  salary,
  FIRST_VALUE(employee_name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) as highest_paid_in_dept,
  LAST_VALUE(employee_name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as lowest_paid_in_dept
FROM employees;

-- NTILE: Divide into buckets/quartiles
SELECT
  customer_name,
  total_purchases,
  NTILE(4) OVER (ORDER BY total_purchases DESC) as quartile,
  CASE NTILE(4) OVER (ORDER BY total_purchases DESC)
    WHEN 1 THEN 'Top 25%'
    WHEN 2 THEN 'Upper Middle'
    WHEN 3 THEN 'Lower Middle'
    WHEN 4 THEN 'Bottom 25%'
  END as customer_segment
FROM customer_totals;

-- Cumulative distribution
SELECT
  employee_name,
  salary,
  CUME_DIST() OVER (ORDER BY salary) as cumulative_dist,
  PERCENT_RANK() OVER (ORDER BY salary) as percent_rank
FROM employees;

-- Complex frame specification
SELECT
  date,
  sales,
  -- Sum of current row and 2 before/after
  SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
  ) as smoothed_sales,
  -- Average from start of month to current row
  AVG(sales) OVER (
    PARTITION BY DATE_TRUNC('month', date)
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as month_to_date_avg
FROM daily_sales;

-- Find gaps in sequences
SELECT
  id,
  LAG(id) OVER (ORDER BY id) as prev_id,
  id - LAG(id) OVER (ORDER BY id) as gap
FROM orders
WHERE id - LAG(id) OVER (ORDER BY id) > 1;

-- Top N per group
WITH ranked_products AS (
  SELECT
    category,
    product_name,
    sales,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY sales DESC
    ) as rank
  FROM product_sales
)
SELECT category, product_name, sales
FROM ranked_products
WHERE rank <= 3;

-- Running difference
SELECT
  month,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) as month_over_month_change,
  (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
    / LAG(revenue) OVER (ORDER BY month) as pct_change
FROM monthly_revenue;

2 files · sql Explain with highlit

Window functions perform calculations across row sets without grouping. ROWNUMBER assigns unique sequential numbers. RANK/DENSERANK handle ties differently. I use PARTITION BY to reset calculations per group. ORDER BY determines calculation order within partitions. LAG/LEAD access previous/next rows—useful for deltas and trends. FIRSTVALUE/LASTVALUE grab boundary values. Running totals use cumulative SUM. Moving averages calculate trends. NTILE divides data into buckets. Window functions avoid self-joins and subqueries. RANGE vs ROWS defines window frames differently. Understanding window functions unlocks complex analytics in single queries. They're essential for reporting and data analysis.