SQL Window Functions Practice: Four Worked PostgreSQL Problems

Create an eight-row dataset you can check by hand

Run this setup once in a PostgreSQL session and the following queries in that same session. The temporary table disappears when the session ends. Before repeating the setup, run DROP TABLE practice_orders. All amounts are positive, and each order has one customer. Amount ties, date ties and missing dates are deliberate.

Reference totals are 550 for customer a and 360 for b. Check them to catch a missing partition. The eight rows are the complete eligible history here; a real report must also define how refunds, canceled orders and currencies affect its input.

CREATE TEMP TABLE practice_orders (
  order_id integer PRIMARY KEY,
  customer text NOT NULL,
  ordered_on date NOT NULL,
  amount numeric(10, 2) NOT NULL
);

INSERT INTO practice_orders VALUES
  (1, 'a', '2026-09-01', 100),
  (2, 'a', '2026-09-02', 200),
  (3, 'a', '2026-09-02', 200),
  (4, 'a', '2026-09-05', 50),
  (5, 'b', '2026-09-01', 80),
  (6, 'b', '2026-09-03', 120),
  (7, 'b', '2026-09-04', 120),
  (8, 'b', '2026-09-07', 40);

SELECT * FROM practice_orders
ORDER BY customer, ordered_on, order_id;

State the question at the right row level

Keep one output row per order. A grouped customer total would collapse each history; a window total preserves orders beside the total. Partitioning chooses which orders influence one another. Ordering inside OVER controls calculation order; the final ORDER BY controls display order.

For each calculation, choose the whole history, an ordered prefix or neighboring rows. Clarify ties whenever the requirement says latest, highest or previous. A unique order_id makes a sequence reproducible, but must not silently redefine business ties.

• PostgreSQL: window functions tutorial

Problem 1: return exactly two orders per customer

The first statement shows three ranking policies. For customer a, the displayed order IDs are 2, 3, 1, 4. Their position values are 1, 2, 3, 4; competition_rank values are 1, 1, 3, 4; value_rank values are 1, 1, 2, 3. Customer b has the same rank pattern for IDs 6, 7, 5, 8. The equal amounts remain tied in RANK and DENSE_RANK because those windows order only by amount.

The second statement answers the exact-two requirement with ROW_NUMBER and an explicit order_id tie breaker. Expected output is a: orders 2 and 3, both 200; b: orders 6 and 7, both 120. To return the two highest distinct amounts instead, calculate DENSE_RANK by amount and filter its value to at most two: orders 1 and 5 would also qualify. RANK <= 2 would exclude those second distinct amounts because their competition rank is three.

Do not add order_id to every window mechanically. It is appropriate for choosing a stable sequence, but including it in the ranking windows would make each row a separate peer and eliminate the amount ties. Explain which version the product requirement needs before presenting the query.

SELECT customer, order_id, amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer ORDER BY amount DESC, order_id
  ) AS position,
  RANK() OVER (
    PARTITION BY customer ORDER BY amount DESC
  ) AS competition_rank,
  DENSE_RANK() OVER (
    PARTITION BY customer ORDER BY amount DESC
  ) AS value_rank
FROM practice_orders
ORDER BY customer, amount DESC, order_id;

WITH numbered AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer ORDER BY amount DESC, order_id
  ) AS position
  FROM practice_orders
)
SELECT customer, order_id, amount
FROM numbered
WHERE position <= 2
ORDER BY customer, position;

• PostgreSQL: window function reference
• PostgreSQL: SELECT processing and filtering

Problem 2: running spend and percentage of the customer total

This report needs two different windows. running_amount follows the customer's chronological order history; customer_total uses the whole partition. The percentage denominator must stay at 550 or 360 for every order from that customer. The explicit ROWS frame makes the running amount advance one ordered row at a time, including the two orders on September 2 separately.

Expected running amounts for order IDs 1 through 4 are 100, 300, 500, 550. For IDs 5 through 8 they are 80, 200, 320, 360. Expected percentages are 18.18, 36.36, 36.36, 9.09 for a and 22.22, 33.33, 33.33, 11.11 for b. Rounded percentages need not add to exactly 100. NULLIF protects the denominator if a later version allows histories whose amounts sum to zero.

Remove order_id and the explicit frame from the running window. With only ordered_on, the default frame includes date peers: both September 2 orders show 500. Predict this difference before running the experiment: it suits a date-level cumulative report, but our question asks for order-by-order progress.

SELECT customer, order_id,
  SUM(amount) OVER (
    PARTITION BY customer ORDER BY ordered_on, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount,
  SUM(amount) OVER (PARTITION BY customer) AS customer_total,
  ROUND(100.0 * amount / NULLIF(
    SUM(amount) OVER (PARTITION BY customer), 0
  ), 2) AS percentage
FROM practice_orders
ORDER BY customer, ordered_on, order_id;

• PostgreSQL: window expression and frame syntax

Problem 3: compare an order with the previous order

LAG retrieves a value from an earlier row in the specified sequence. Here previous means earlier in the customer's order history, with order_id settling same-date ordering. It does not mean yesterday. Customer a has no order on September 3 or 4, yet the September 5 order still compares with September 2's order 3.

Expected changes for IDs 1 through 4 are NULL, 100, 0, -150. For IDs 5 through 8 they are NULL, 40, 0, -80. NULL in the first row expresses missing history. Replacing it with zero would imply a previous zero-value order and change the interpretation. The CTE names previous_amount so the subtraction is easy to read and inspect.

For decreases only, add WHERE amount < previous_amount outside compared: orders 4 and 8 remain. Filtering low amounts inside would change predecessors. When debugging, check the preceding order ID as well as the arithmetic.

WITH compared AS (
  SELECT customer, order_id, ordered_on, amount,
    LAG(amount) OVER (
      PARTITION BY customer ORDER BY ordered_on, order_id
    ) AS previous_amount
  FROM practice_orders
)
SELECT customer, order_id, previous_amount,
  amount - previous_amount AS change
FROM compared
ORDER BY customer, ordered_on, order_id;

• PostgreSQL: window function reference
• PostgreSQL: SELECT processing and filtering

Problem 4: average the current and previous order

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW includes at most two rows in the customer's ordered partition. The first order has only itself available. Expected averages for IDs 1 through 4 are 100.00, 150.00, 200.00, 125.00. For IDs 5 through 8 they are 80.00, 100.00, 120.00, 80.00.

This is a two-order average: b's final pair spans September 4 and 7. A calendar report must define missing days, often using a daily aggregate or calendar table first. Decide whether same-day orders should carry separate weight or be combined.

SELECT customer, order_id,
  ROUND(AVG(amount) OVER (
    PARTITION BY customer ORDER BY ordered_on, order_id
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ), 2) AS two_order_average
FROM practice_orders
ORDER BY customer, ordered_on, order_id;

• PostgreSQL: window expression and frame syntax

Mistakes that produce plausible but wrong reports

Inspect every result row while changing a query. Successful execution does not prove business correctness. On larger data, begin with one customer whose history includes a tie and a gap; these expose assumptions ordinary rows conceal.

• Filtering position in the same SELECT's WHERE: calculate the window in a CTE or subquery, then apply the condition outside it.
• Filtering the input without discussing scope: ranking September orders is different from ranking all orders and displaying September's winners.
• Relying on the final ORDER BY to settle a window tie: put the deterministic sequence inside the relevant OVER clause too.
• Joining each order to several line items before summing: inspect the input row count and aggregate back to one row per order when that is the intended unit.

• PostgreSQL: SELECT processing and filtering

A 20-minute interview exercise with a checkable answer

Without copying the worked queries, return each customer's latest order, its preceding amount, and the lifetime total. Calculate all three against the complete history before choosing the latest row. Use chronological ordering for LAG, reverse chronological ordering for the latest-row marker, and an unordered partition for the lifetime sum. Explain why the functions need different orderings.

Your answer should contain a: order 4, amount 50, previous amount 200, total 550; b: order 8, amount 40, previous amount 120, total 360. If previous_amount is NULL for both winners, you probably selected the latest rows before calculating LAG. Finish by adding a same-date latest order and stating your tie policy before running the query.

Turn missed decisions into retrieval questions about filtering, ties or frames. Revisit them in RecallDeck, then solve the query again from the requirement. Knowing a function name is only the beginning.

Quick answers

Frequently asked questions