SQL NTH_VALUE
Returns the value from a specific position in the window — and has the exact same frame gotcha as LAST_VALUE.
What & Why
NTH_VALUE(column, n) returns the value from the nth row of the window. Like LAST_VALUE, it's affected by the frame — asking for a position past where the default frame currently ends returns NULL until the frame catches up.
See How It Works
BUSINESS QUESTION
Growth shows the third recorded DAU value beside every daily row.
| date_day | dau | new_users | returning_users |
|---|---|---|---|
| 2024-04-01 | 1240 | 180 | 1060 |
| 2024-04-02 | 1315 | 205 | 1110 |
| 2024-04-03 | 1288 | 164 | 1124 |
| 2024-04-04 | 1392 | 221 | 1171 |
EXAMPLE QUERY
SELECT
date_day,
dau,
NTH_VALUE(dau, 3) OVER (
ORDER BY date_day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third_day_dau
FROM growth.daily_active_users
ORDER BY date_day;RESULT — third recorded DAU
| date_day | dau | third_day_dau |
|---|---|---|
| 2024-04-01 | 1240 | 1288 |
| 2024-04-02 | 1315 | 1288 |
| 2024-04-03 | 1288 | 1288 |
| 2024-04-04 | 1392 | 1288 |
The third row in date order contains DAU 1,288.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario