SQL Window Functions: Analytics Made Easy

Window functions perform calculations across a set of rows related to the current row.

Ranking Functions

-- ROW_NUMBER: unique sequential number
SELECT
  employee_id,
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- RANK: ties get same 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 game_scores;

-- Score: 100, 100, 90 -> RANK: 1, 1, 3 | DENSE_RANK: 1, 1, 2

-- NTILE: divide rows into N equal groups
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;
-- 1=bottom 25%, 2=25-50%, 3=50-75%, 4=top 25%

LAG and LEAD

-- Compare with previous/next row
SELECT
  order_date,
  daily_revenue,
  LAG(daily_revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
  LEAD(daily_revenue) OVER (ORDER BY order_date) AS next_day_revenue,
  daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date) AS day_over_day_change,
  ROUND(
    100.0 * (daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date))
    / NULLIF(LAG(daily_revenue) OVER (ORDER BY order_date), 0),
    2
  ) AS pct_change
FROM daily_sales
ORDER BY order_date;

-- Month over month comparison
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(amount) AS monthly_revenue,
  LAG(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS prev_month_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Running Totals and Cumulative Statistics

-- Running total
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total,
  AVG(amount) OVER (ORDER BY order_date) AS running_avg,
  COUNT(*) OVER (ORDER BY order_date) AS running_count
FROM orders;

-- Cumulative percentage
SELECT
  product_name,
  revenue,
  SUM(revenue) OVER () AS total_revenue,
  ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total,
  ROUND(100.0 * SUM(revenue) OVER (ORDER BY revenue DESC) / SUM(revenue) OVER (), 2) AS cumulative_pct
FROM product_revenue
ORDER BY revenue DESC;
-- Pareto analysis: identify products making up 80% of revenue

Moving Averages

-- 7-day moving average
SELECT
  date,
  daily_users,
  AVG(daily_users) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- Current + 6 previous = 7 days
  ) AS ma_7day,
  AVG(daily_users) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW  -- 30-day moving average
  ) AS ma_30day
FROM daily_metrics;

-- Range-based window (not row count)
SELECT
  sale_date,
  amount,
  AVG(amount) OVER (
    ORDER BY sale_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg
FROM sales;

First/Last Value in Group

-- First purchase date and most recent purchase per customer
SELECT DISTINCT
  customer_id,
  FIRST_VALUE(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS first_purchase,
  LAST_VALUE(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_purchase,
  COUNT(*) OVER (PARTITION BY customer_id) AS total_orders
FROM orders;

Practical Business Examples

-- Top N per category
WITH ranked AS (
  SELECT
    category,
    product_name,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM product_sales
)
SELECT category, product_name, revenue
FROM ranked
WHERE rn <= 3;  -- Top 3 products per category

-- Session analytics: gap and island problem
WITH sessions AS (
  SELECT
    user_id,
    event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
    CASE
      WHEN LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
           - event_time > INTERVAL '30 minutes'
      THEN 1 ELSE 0
    END AS session_end
  FROM events
)
SELECT user_id, COUNT(*) AS session_count
FROM sessions
WHERE session_end = 1
GROUP BY user_id;

Window functions eliminate the need for self-joins and subqueries in most analytical SQL.