Window Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
SELECT id, total, AVG(total) OVER () AS overall_avg FROM orders;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet.
SELECT customer_id, ordered_at, total,\n SUM(total) OVER (\n PARTITION BY customer_id\n ORDER BY ordered_at, id\n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n ) AS customer_running_total\nFROM orders;The query returns only the intended rows and columns, with deterministic ordering where order matters.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT category,\n COUNT(*) AS products,\n ROUND(AVG(price), 2) AS average_price,\n SUM(stock) AS units_available\nFROM products\nGROUP BY category\nHAVING SUM(stock) > 0;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
ROW_NUMBER — unique sequence within a windowRANK — rank with gaps after tiesDENSE_RANK — rank without gapsPERCENT_RANK — relative rank from 0 to 1CUME_DIST — cumulative distributionNTILE — divide rows into bucketsLAG — value from a previous rowLEAD — value from a following rowFIRST_VALUE — first value in frameLAST_VALUE — last value in frameNTH_VALUE — value at a frame positionCOUNT OVER — running or partition countSUM OVER — running or partition totalAVG OVER — moving or partition averageMIN OVER / MAX OVER — window extremaSTDDEV / VARIANCE OVER — window statisticsPARTITION BY — restart calculation by groupORDER BY — define row sequenceROWS — physical-row frameRANGE — peer/value-based frameGROUPS — peer-group frame; PostgreSQLEXCLUDE — remove frame rows; PostgreSQLStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;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.
Window Functions is a practical SQL concept used to design, query, secure, operate, or analyze relational data correctly.
SELECT id, total, AVG(total) OVER () AS overall_avg FROM orders;Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use.
A sales manager needs a monthly report showing revenue, order counts, rankings, and changes over time without exporting raw data to a spreadsheet.
SELECT customer_id, ordered_at, total,\n SUM(total) OVER (\n PARTITION BY customer_id\n ORDER BY ordered_at, id\n ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW\n ) AS customer_running_total\nFROM orders;The query returns only the intended rows and columns, with deterministic ordering where order matters.
This lesson reuses picostore. Relevant tables: customers, products, orders, order_items. Keep the starter rows from the Introduction lesson so results remain comparable.
SELECT category,\n COUNT(*) AS products,\n ROUND(AVG(price), 2) AS average_price,\n SUM(stock) AS units_available\nFROM products\nGROUP BY category\nHAVING SUM(stock) > 0;Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct.
This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item.
ROW_NUMBER — unique sequence within a windowRANK — rank with gaps after tiesDENSE_RANK — rank without gapsPERCENT_RANK — relative rank from 0 to 1CUME_DIST — cumulative distributionNTILE — divide rows into bucketsLAG — value from a previous rowLEAD — value from a following rowFIRST_VALUE — first value in frameLAST_VALUE — last value in frameNTH_VALUE — value at a frame positionCOUNT OVER — running or partition countSUM OVER — running or partition totalAVG OVER — moving or partition averageMIN OVER / MAX OVER — window extremaSTDDEV / VARIANCE OVER — window statisticsPARTITION BY — restart calculation by groupORDER BY — define row sequenceROWS — physical-row frameRANGE — peer/value-based frameGROUPS — peer-group frame; PostgreSQLEXCLUDE — remove frame rows; PostgreSQLStart with a small customer table and retrieve active customers in a predictable order.
SELECT customer_id, name, email\nFROM customers\nWHERE status = 'active'\nORDER BY name;Use a CTE and a window function to rank customer revenue while keeping the query readable and testable.
WITH customer_revenue AS (\n SELECT customer_id, SUM(total_amount) AS revenue\n FROM orders\n WHERE order_status = 'completed'\n GROUP BY customer_id\n)\nSELECT customer_id, revenue,\n DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank\nFROM customer_revenue\nORDER BY revenue_rank, customer_id;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.