Window functions for advanced analytics

-- 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;