SQL Window Functions Without Collapsing Rows

Window functions calculate over related rows while retaining detail rows. Grouping instead reduces rows to one per group.

Before you start

You should understand tables, keys, joins and SELECT queries. Write down a tiny dataset before reasoning about a query. SQL examples use PostgreSQL-style syntax where relevant; planner behavior, locking and available features must be checked for the database and version used in an application.

The practical goal is to reason through this situation: Show each sale alongside its customer’s running total. Read the walkthrough first, then try the interview exercise before opening its answer. The important part is explaining the decision and its consequences, rather than remembering a definition alone.

Step-by-step walkthrough

Step 1: Keep detail rows

A window adds a related aggregate without collapsing each sale into a group total.

Step 2: Define partition and order

Select customer ownership and a deterministic tie-breaker.

Step 3: Choose the frame

Use an explicit ROWS frame for a row-by-row running total.

Worked scenario

Show each sale alongside its customer’s running total.

SELECT id, customer_id, amount,
 SUM(amount) OVER (
  PARTITION BY customer_id ORDER BY sold_at, id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS running_total
FROM sales;

sql

The ID breaks timestamp ties. Each sale remains visible while its running total includes preceding rows in that customer’s chosen order.

Common mistake

Unspecified window framing can surprise calculations when ordering values tie.

Verify the behavior

Test equal timestamps and multiple customers; compare the final running total with each customer’s ordinary sum.

Interview exercise

Calculate a deterministic running sum.

Answer and reasoning

Define the partition, a tie-breaking order and the intended ROWS frame rather than relying on an implicit frame.

Continue learning

Compare the scenario with the SQL and PostgreSQL interview questions and test your understanding with the SQL and PostgreSQL MCQs. For terminology and implementation details, consult the reference material.