FIRST, LAST, NTH VALUE

Concepts covered: First & Last Values

FIRST_VALUE returns the value from the first row of the window, in the window's order. Where LAG reaches a fixed distance back, FIRST_VALUE reaches all the way to the start, so every row in a partition gets the same answer: the top of the list. Order the partition by streams descending and the first row is the artist's biggest track, which makes FIRST_VALUE the function for measuring every row against the leader.

SELECT
  artist,
  track,
  streams,
  FIRST_VALUE(streams) OVER (
    PARTITION BY artist
    ORDER BY streams DESC
  ) AS top_streams
FROM artist_tracks

Orbit tops Nova's partition, so all 3 Nova rows, Dust and Halo included, read 4000 from it. Kite's 2 rows read 2500 from Wind. Dividing each row's streams by that column turns raw counts into a share of the leader, which is the number a label manager wants: Dust pulls 75 percent of what Orbit pulls, and Line pulls 20 percent of Wind.

SELECT
  artist,
  track,
  streams,
  ROUND(100.0 * streams / FIRST_VALUE(streams) OVER (
    PARTITION BY artist
    ORDER BY streams DESC
  ), 1) AS pct_of_top
FROM artist_tracks

LAST_VALUE sees less than you expect

LAST_VALUE ought to hand every row the bottom of the partition. Run it with ORDER BY streams DESC and each row gets its own track back instead. The cause is how an ordered window is sized: once an OVER clause has an ORDER BY, the rows it covers run from the start of the partition up to the current row (and any rows tied with it, as the next section shows) and no further. That rule is why SUM over an ordered window produced a running total in the beginner lesson, and it means the last row LAST_VALUE can see is the current one. The 3 tabs below show the surprise and 2 ways out of it.

Default

The ordered window ends at the current row, so LAST_VALUE hands every row its own track.

SELECT
  artist,
  track,
  streams,
  LAST_VALUE(track) OVER (
    PARTITION BY artist
    ORDER BY streams DESC
  ) AS bottom_track
FROM artist_tracks

Full window

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING stretches the window over the whole partition, so every row sees the true last track.

SELECT
  artist,
  track,
  streams,
  LAST_VALUE(track) OVER (
    PARTITION BY artist
    ORDER BY streams DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS bottom_track
FROM artist_tracks

Flip order

Sorting ascending makes the smallest track the first row, so FIRST_VALUE returns it with no extra clause at all.

SELECT
  artist,
  track,
  streams,
  FIRST_VALUE(track) OVER (
    PARTITION BY artist
    ORDER BY streams ASC
  ) AS bottom_track
FROM artist_tracks

The first tab returns Wind, Line, Orbit, Dust, Halo, one row's own track per row. The other 2 tabs both return Line for every Kite row and Halo for every Nova row. The second tab fixes the window's size, which is the subject of the next section; the third sidesteps the question by making the row you want the first one. Both are correct, and the third is shorter, so reach for it when a single reversed sort gets you there.

NTH_VALUE picks a position

NTH_VALUE(track, 2) returns the value from the second row of the window. The same sizing rule applies: under an ordered window with no extra clause, the first row of a partition has not reached row 2 yet, so it gets NULL while every later row gets the runner-up. Stretching the window to the whole partition, as the Full window tab did, gives every row the same runner-up.

SELECT
  artist,
  track,
  streams,
  NTH_VALUE(track, 2) OVER (
    PARTITION BY artist
    ORDER BY streams DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS runner_up
FROM artist_tracks

Kite has 2 tracks, so its runner-up is Line on both rows. A partition with a single track would return NULL from NTH_VALUE(track, 2) on every row, because the second row does not exist. That NULL is informative: a COALESCE to the text 'none' turns it into a readable label in a report.

On artist_tracks, what does FIRST_VALUE(track) OVER (PARTITION BY artist ORDER BY streams ASC) return for Nova's Orbit row?

• Orbit, because the function reads the current row
• Halo, the Nova track with the fewest streams
• Dust, the second row of the ascending order
• NULL, since Orbit is the last row and the window stops there

About this lesson

This section is part of the Window Functions: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.