Think of these four functions as your complete navigation system through data rows. LAG and LEAD are like GPS directions - LAG tells you where you came from ("previous exit was 2 miles back"), LEAD tells you what's coming next ("next exit in 1 mile"). FIRST_VALUE and LAST_VALUE are like looking at the start and end of your journey - FIRST_VALUE shows your starting point, LAST_VALUE shows your destination. Together, they let you compare any row to its neighbors, boundaries, or reference points. Perfect for answering "How does this compare to where we started?" or "What's the difference from our peak performance?"

**

**LAG() and LEAD() - The Time Travelers (Review):**

We covered these in detail previously, but here's a quick refresher:

- **LAG()**: Accesses previous rows - perfect for period-over-period comparisons

- **LEAD()**: Accesses future rows - ideal for forecasting and predictions

**FIRST_VALUE() - The Baseline Anchor:**

FIRST_VALUE returns the first value in the window frame, making it perfect for baseline comparisons.

```sql

-- Compare each day's sales to the first day of the month

SELECT

date,

daily_sales,

FIRST_VALUE(daily_sales) OVER (

PARTITION BY DATE_TRUNC('month', date)

ORDER BY date

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) as first_day_sales,

daily_sales - FIRST_VALUE(daily_sales) OVER (

PARTITION BY DATE_TRUNC('month', date)

ORDER BY date

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

) as vs_first_day

FROM daily_sales;

```

**FIRST_VALUE Use Cases:**

- ✅ **Baseline comparisons**: Current vs opening price, vs first purchase

- ✅ **Performance tracking**: Current vs initial goal, vs starting point

- ✅ **Cohort analysis**: Compare to first period in each cohort

- ✅ **Trend analysis**: How far have we moved from the beginning?

**LAST_VALUE() - The Final Destination:**

LAST_VALUE returns the last value in the window frame. **Critical**: Default frame ends at current row, so you usually need to specify the full frame.

```sql

-- WRONG: Default frame gives unexpected results

SELECT

date,

stock_price,

LAST_VALUE(stock_price) OVER (ORDER BY date) as last_price

FROM stock_prices;

-- Problem: Each row shows itself as "last" value!