Lead

Window functions in PostgreSQL allow you to perform calculations across sets of rows related to the current row, without collapsing rows like GROUP BY does. They’re essential for solving ranking, running totals, moving averages, and row-by-row comparisons. This post reviews key window functions with practical, interview-friendly examples. 1. OVER (PARTITION BY ...) Window functions let you run aggregations without reducing the number of rows. A correlated subquery alternative: SELECT transaction_id, payment_type, customer_id, price_in_transaction, (SELECT SUM(price_in_transaction) FROM sales s2 WHERE s1.customer_id = s2.customer_id) AS total_by_customer FROM sales s1; This works but is inefficient. Instead, use: ...