SQL Interview Prep · Lesson

FIRST_VALUE, LAST_VALUE and Frame Edges

Pulling boundary values and the LAST_VALUE frame gotcha.

FIRST_VALUE, LAST_VALUE and Frame Edges is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

The Default Window Frame

Here is the crux. When you add ORDER BY to a window, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

That means the window for each row spans only from the partition start up to the current row, not to the end. FIRST_VALUE is unaffected (the first row is always in range), but LAST_VALUE is badly affected.

The LAST_VALUE Trap

Run LAST_VALUE with just an ORDER BY and most candidates expect the partition's final value. Instead, because the frame ends at the current row, the "last value in frame" is just the current row's own value.

So this query returns login_date itself on every row, which looks broken. This is the single most-asked window-function gotcha.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS wrong_last_login
FROM logins;

Fixing LAST_VALUE With a Full Frame

The fix is to widen the frame to cover the entire partition: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Now the window for every row spans the whole partition, so LAST_VALUE returns the true final value. State this fix explicitly in an interview; it proves you understand frames, not just function names.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS last_login
FROM logins;

An Easier Alternative

Many engineers sidestep the frame entirely: to get the last value, use FIRST_VALUE with the reversed sort order.

FIRST_VALUE(login_date) OVER (... ORDER BY login_date DESC) returns the latest date with no frame clause needed. It is a clean, memorable trick to mention.

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

ROWS vs RANGE in Frames

Frames come in two flavors. ROWS counts physical rows; RANGE groups by equal ORDER BY values (peers).

The default frame uses RANGE, which is why tied order-by values share a frame boundary. For LAST_VALUE fixes, prefer the explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to avoid surprises with ties.

NTH_VALUE for Arbitrary Positions

Beyond first and last, NTH_VALUE(col, n) grabs the value at position n within the frame, for example the second-highest price.

It obeys the same frame rules as LAST_VALUE, so combine it with a full frame when you want the nth value across the whole partition rather than just up to the current row.

SELECT
  product_id,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY product_id
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS second_highest_price
FROM prices;

Worked Example: First and Last Together

A common report shows each transaction next to the customer's first and last transaction amount. Combine both functions, remembering the explicit frame for LAST_VALUE.

Now every row carries the full-partition first and last, ready for a delta or a labeling step.

SELECT
  customer_id,
  txn_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  LAST_VALUE(amount) OVER w AS last_amt
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Named Windows Keep It DRY

Notice the previous query used a WINDOW w AS (...) clause and referenced OVER w twice. Defining the window once avoids repeating a long frame spec and prevents the two functions from drifting apart.

Most major databases support named windows. Using one is a clean touch that interviewers appreciate when several columns share a window.

Worked Example: First-to-Last Delta

A frequent follow-up is the change from a customer's first to last transaction. With both boundary values on every row, subtract them, then deduplicate to one row per customer if needed.

This combines the full-frame fix with simple arithmetic, the kind of end-to-end answer interviewers want to see assembled cleanly.

SELECT DISTINCT
  customer_id,
  LAST_VALUE(amount) OVER w - FIRST_VALUE(amount) OVER w AS first_to_last_delta
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Quick Check

The classic LAST_VALUE gotcha.

Recap

Boundary-value functions hinge on the frame:

FIRST_VALUE works under the default frame; LAST_VALUE does not.
The default frame ends at the current row, so fix LAST_VALUE with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or flip the sort and use FIRST_VALUE.
NTH_VALUE(col, n) grabs arbitrary positions; named windows keep multi-column specs DRY.

That completes the LAG, LEAD, NTILE, and boundary-value toolkit.

Frequently asked questions

Is the “FIRST_VALUE, LAST_VALUE and Frame Edges” lesson free?

Yes — the full text of “FIRST_VALUE, LAST_VALUE and Frame Edges” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “FIRST_VALUE, LAST_VALUE and Frame Edges”?

Pulling boundary values and the LAST_VALUE frame gotcha. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “FIRST_VALUE, LAST_VALUE and Frame Edges” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep