Introduction
Simple OVER()
PARTITION BY
Ranking functions
Window frame
19. Window frame – Revision
Analytic functions
ORDER BY PARTITION BY
Order of evaluation
Summary

Instruction

Good job. Let's continue the adventure. In Part 5, we learned window frames. Let's take a look at the following example:

SELECT
  id,
  payment_date,
  payment_amount,
  SUM(payment_amount) OVER(ORDER BY payment_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM subscription;

In the above query, we calculate a running total paid for all subscriptions up to the current row.

Exercise

For each single rental, show the id, rental_date, payment_amount and the running total of payment_amounts of all rentals from the oldest one (in terms of rental_date) until the current row.

Stuck? Here's a hint!

Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Console

Code editor

Result

TableConsole