Hey fellow engineer 👋, when I started exploring window functions preparing to write this article, I thought I would just write a short article covering the basics and a few simple use cases. But the more time I spent working with real queries, the more I realized how powerful (and sometimes tricky) they can be. So instead of a quick overview, this turned into a complete deep dive on window functions.

🚀 Summary: By the end of this article, you will know:

• what window functions are
• when and why you should use them
• how to write them in SQL
• the different types of window functions you will probably need in practice

💬 TL;DR:

• Aggregate functions collapse rows into one result per group.
• Window functions keep all rows and add extra context(same per group) per row.
• Use them for running totals, rankings, top-N queries, comparisons with previous/next rows, and building arrays.
• They’re essential for analytics queries in Postgres (and most SQL dialects).

✅ Most common use cases for window functions:

• Ranking rows (ROW_NUMBER, RANK, DENSE_RANK)
• Selecting Top-N per group (e.g. top 3 invoices per customer)
• Running totals and cumulative sums (SUM() OVER ... ORDER BY)
• Comparing current row with previous/next row (LAG, LEAD)
• Finding first/last values in a group (FIRST_VALUE, LAST_VALUE)
• Collecting values into arrays or strings (ARRAY_AGG, STRING_AGG)

🧑‍🌾 Initial DB setup

Let’s create two tables: customers and invoices and seed it with some data. Customers’ table has two columns id and name, invoices - id, customer_id, amount and date.