SQL FIRST_VALUE
Returns the value from the very first row of the window, repeated on every row.
What & Why
FIRST_VALUE returns the value from the first row in the current ordered window frame. It compares every row with a baseline value without joining the first row back to the detail table.
See How It Works
BUSINESS QUESTION
Compare each daily active-user value with the first observed day.
| 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,
FIRST_VALUE(dau) OVER (
ORDER BY date_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS first_dau
FROM growth.daily_active_users
ORDER BY date_day;RESULT — first DAU repeated as the baseline
| date_day | dau | first_dau |
|---|---|---|
| 2024-04-01 | 1240 | 1240 |
| 2024-04-02 | 1315 | 1240 |
| 2024-04-03 | 1288 | 1240 |
| 2024-04-04 | 1392 | 1240 |
FIRST_VALUE reads 1,240 from the first ordered row and repeats it beside every later day.
Sign up free to try it on a real business scenario