Window Functions

Lesson content

Window Functions Window Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly. Example SELECT id, total, AVG(total) OVER () AS overall_avg FROM orders; Key point Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use. Real-life example A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet. Advanced example SELECT customer_id, ordered_at, total, SUM(total) OVER ( PARTITION BY customer_id ORDER BY ordered_at, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS customer_running_total FROM orders; Expected result The query returns only the intended rows and columns, with deterministic ordering where order matters. Production check Test with empty, duplicate, null, and boundary values. Use a transaction for related writes. Inspect the execution plan before adding an index. Use parameterized queries for application input. Continue with the PicoStore database This lesson reuses picostore . Relevant tables: customers, products, orders, order_items . Keep the starter rows from the Introduction lesson so results remain comparable. Another practical example SELECT category, COUNT(*) AS products, ROUND(AVG(price), 2) AS average_price, SUM(stock) AS units_available FROM products GROUP BY category HAVING SUM(stock) > 0; Check the result Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct. SQL reference catalog: Window Functions This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item. Ranking functions ROW_NUMBER — unique sequence within a window RANK — rank with gaps after ties DENSE_RANK — rank without gaps PERCENT_RANK — relative rank from 0 to 1 CUME_DIST — cumulative distribution NTILE — divide rows into buckets Navigation/value functions LAG — value from a previous row LEAD — value from a following row FIRST_VALUE — first value in frame LAST_VALUE — last value in frame NTH_VALUE — value at a frame position Aggregates used as windows COUNT OVER — running or partition count SUM OVER — running or partition total AVG OVER — moving or partition average MIN OVER / MAX OVER — window extrema STDDEV / VARIANCE OVER — window statistics Window definition parts PARTITION BY — restart calculation by group ORDER BY — define row sequence ROWS — physical-row frame RANGE — peer/value-based frame GROUPS — peer-group frame; PostgreSQL EXCLUDE — remove frame rows; PostgreSQL Easy example Start with a small customer table and retrieve active customers in a predictable order. SELECT customer_id, name, email FROM customers WHERE status = 'active' ORDER BY name; How to verify the easy example Run it with representative input. Confirm the expected output. Try one missing, invalid, or boundary value. Advanced example Use a CTE and a window function to rank customer revenue while keeping the query readable and testable. WITH customer_revenue AS ( SELECT customer_id, SUM(total_amount) AS revenue FROM orders WHERE order_status = 'completed' GROUP BY customer_id ) SELECT customer_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM customer_revenue ORDER BY revenue_rank, customer_id; Advanced review Explain the tradeoffs and assumptions. Test failure, scale, security, and recovery behavior. Capture evidence from tests, execution plans, logs, or review output. Additional practical guidance Window Functions: MySQL and PostgreSQL Prefer standard SQL functions when portability matters. MySQL and PostgreSQL differ most in date formatting, type conversion, string aggregation, and error handling for invalid values. Required verification Run the simple case. Test a NULL, duplicate, empty, or boundary case where relevant. Confirm the affected rows or query result. Use EXPLAIN for performance-sensitive queries.