SQL Window Functions Explained with Practical Examples

Show every sale alongside customer spending

An analyst wants one invoice per row, with the customer’s total spending beside it. GROUP BY provides totals but removes invoice detail. Joining a grouped subquery adds that detail back. A window aggregate often states the request more directly.

A window function calculates a value over a related set of query rows while preserving individual rows. OVER defines that set, its ordering and, for frame-sensitive functions, the portion used in the calculation. Think of it as adding an analytical column without automatically collapsing the input.

We will move from customer totals to ranks, cumulative revenue, moving averages and neighboring invoices. The hard part is choosing the correct population, tie policy and frame—not remembering a function name.

The Chinook sample database

These examples use the complete Chinook music-store database available in the DataTug demo: 59 customers and 412 invoices, with every original table and value retained. All 59 customers have invoices. Queries that focus on one customer or qualifying invoices show that condition explicitly in the SQL.

The relationship is Customer.CustomerId → Invoice.CustomerId: one customer can have many invoices; each invoice belongs to one customer. InvoiceId is the invoice’s unique key. InvoiceDate is uniformly formatted YYYY-MM-DD HH:MM:SS text, which supports the date comparisons and ordering shown here; Total is a numeric amount in the dataset’s units. This source uses dates from 2021–2025; other Chinook releases differ. Use InvoiceDate rather than assuming invoice IDs guarantee date order.

Results are shown as a preview for clarity. Tables with more than 10 records initially show the first 10. Choose Show all to reveal every matching record, or Run to execute the exact displayed SQL in the DataTug editor against the same Chinook database. No query is saved to the project. The preview does not add a WHERE or LIMIT clause. Synthetic examples define their own rows with VALUES in the displayed query.

Download the complete Chinook SQLite database and open it with SQLite 3.25.0 or later, then run any query below. No account or network database is required. Query downloads contain every displayed example and exercise solution. Frozen result tables are checked against this database; each code header states its minimum SQLite version.

Chinook upstream and schema · Chinook licence · Source attribution. SQLite uses binary floating-point for many decimal operations; examples round money for display. Production finance needs an explicit exact-decimal or integer-minor-unit policy.

GROUP BY collapses; a window annotates

A grouped aggregate returns one row per group. A window aggregate computes across a partition but retains input rows. Compare the source and outputs, and highlight a customer to see how their total repeats.

SELECT InvoiceId, CustomerId, InvoiceDate, Total
FROM Invoice
ORDER BY CustomerId, InvoiceDate, InvoiceId;

SELECT CustomerId, ROUND(SUM(Total), 2) AS CustomerTotal
FROM Invoice GROUP BY CustomerId ORDER BY CustomerId;

SELECT InvoiceId, CustomerId, Total,
 ROUND(SUM(Total) OVER (PARTITION BY CustomerId), 2) AS CustomerTotal
FROM Invoice
ORDER BY CustomerId, InvoiceDate, InvoiceId;

All three panels use the same 412 invoices. Highlight customer 1 to compare their seven source invoices, one summary row and seven annotated window rows. Do not sum a repeated customer-total column as if it were an invoice amount.

Windows see rows surviving the current WHERE and grouping. Filter to one customer first and an unpartitioned total means that customer’s total. Group first and the window sees grouped rows; it does not reach back into every raw table row.

OVER() and PARTITION BY define the population

SUM(Total) OVER () uses one partition containing the current result. It is useful for shares of a grand total. PARTITION BY CustomerId divides input into customer-specific sets, restarting the calculation for each customer. Partitioning removes no rows.

SELECT InvoiceId, Total,
 ROUND(100.0 * Total / SUM(Total) OVER (), 2) AS PercentOfRevenue
FROM Invoice ORDER BY InvoiceId;

SELECT InvoiceId, CustomerId, Total,
 ROUND(SUM(Total) OVER (PARTITION BY CustomerId), 2) AS CustomerTotal
FROM Invoice
ORDER BY CustomerId, InvoiceDate, InvoiceId;