lesson 42 Window functions · advanced

FIRST_VALUE, LAST_VALUE & frames

Control which rows a window includes when choosing first and last values.

the idea

FIRST_VALUE and LAST_VALUE read a value at the edge of the window frame. With ORDER BY, the default frame ends at the current row, so LAST_VALUE returns the current row. Specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the whole partition, or another frame for running extremes.

Dataset: Finance / Payments

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   See the default window frame. Run this. With ORDER BY and no frame clause, LAST_VALUE usually equals this row’s amount—not the last row in the account. The second column uses a full-partition frame so you can compare.the pattern

   example.sql Run

   sql›

   SELECT
     transaction_id,
     amount_cents,
     LAST_VALUE(amount_cents) OVER (ORDER BY posted_at, transaction_id) AS default_frame,
     LAST_VALUE(amount_cents) OVER (ORDER BY posted_at, transaction_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS whole_partition
   FROM transactions
   WHERE account_id = 3 AND status = 'posted';

2. 2

   Average the last three entries. Run this. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages this posted line and the two before it—a small sliding window on the ledger.

   step-2.sql Run

   sql›

   SELECT
     transaction_id,
     amount_cents,
     AVG(amount_cents) OVER (ORDER BY posted_at, transaction_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_last_three
   FROM transactions
   WHERE account_id = 3 AND status = 'posted';

common mistake

LAST_VALUE(x) OVER (ORDER BY t) without a frame returns the current row’s x, not the last row’s.