SQL Interview Prep · Lesson

FIRST_VALUE, LAST_VALUE and Frame Edges

Pulling boundary values and the LAST_VALUE frame gotcha.

Pulling Boundary Values

Interviewers ask: "Show each row alongside the first and last value in its group." Think first login date per user, or the latest price in a partition next to every detail row.

The functions are FIRST_VALUE and LAST_VALUE. They look simple, but LAST_VALUE hides one of the most famous window-frame gotchas in SQL. This lesson makes both reliable.

FIRST_VALUE Basics

FIRST_VALUE(col) returns the value of col from the first row of the window, attached to every row. Ordered by date, it gives each row the earliest value in its partition.

Because the default frame starts at the partition's first row, FIRST_VALUE usually behaves exactly as people expect.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS first_login
FROM logins;

All lessons in this course

← Back to SQL Interview Prep