Window Functions

Key Characteristics

• ▶Compute across ordered sets of rows (windows) without reducing row count
• ▶Support rank functions (ROW_NUMBER, RANK, DENSE_RANK)
• ▶Enable running aggregates (SUM OVER, AVG OVER)
• ▶Provide access to adjacent rows (LAG, LEAD)
• ▶Operate within partitions for GROUP BY-like functionality
• ▶Preserve individual rows while adding computed columns

Why It Matters

• ▶Simplify complex analytical logic that previously required subqueries or procedural code
• ▶Enable running total and trend calculations for time-series analysis
• ▶Compute rankings and percentiles without separate aggregation steps
• ▶Reduce query complexity and improve readability
• ▶Enable comparative metrics (current vs. average, current vs. previous)
• ▶Dramatically improve query performance through single-pass computation

Example

`
-- Window function examples

-- 1. Running total by customer
SELECT 
  customer_id,
  order_date,
  order_value,
  SUM(order_value) OVER (
    PARTITION BY customer_id 
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM orders
ORDER BY customer_id, order_date;

-- 2. Rank customers by total spend
SELECT 
  customer_id,
  SUM(order_value) as total_spent,
  RANK() OVER (ORDER BY SUM(order_value) DESC) as spend_rank,
  DENSE_RANK() OVER (ORDER BY SUM(order_value) DESC) as dense_rank
FROM orders
GROUP BY customer_id;

-- 3. Compare each order to customer average
SELECT 
  customer_id,
  order_date,
  order_value,
  AVG(order_value) OVER (PARTITION BY customer_id) as customer_avg,
  order_value - AVG(order_value) OVER (PARTITION BY customer_id) as variance
FROM orders;

-- 4. Access previous and next rows
SELECT 
  order_date,
  revenue,
  LAG(revenue) OVER (ORDER BY order_date) as prior_day_revenue,
  LEAD(revenue) OVER (ORDER BY order_date) as next_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY order_date) as day_over_day_change
FROM daily_summary
ORDER BY order_date;

-- 5. Percentile calculations
SELECT 
  region,
  customer_id,
  order_value,
  PERCENT_RANK() OVER (PARTITION BY region ORDER BY order_value) as region_percentile
FROM orders;
`

Coginiti Perspective

Coginiti's Semantic SQL fully supports window functions including ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, and NTH_VALUE, along with PARTITION BY and frame specifications. The semantic layer translates window function syntax to each platform's dialect, handling differences like BigQuery and Snowflake's RANGE frame conversions and Redshift's COALESCE rewrites for LAG/LEAD defaults. This means analysts write window functions once in Semantic SQL and get correct behavior across all connected platforms.

Related Concepts