Introduction
LEAD() and LAG()
FIRST_VALUE(), LAST_VALUE()
15. The FIRST_VALUE(x) function
Summary

Instruction

Good job! LEAD() and LAG() are always relative to the current row. Now we'll get to know three other functions that are independent of the current row.

The first one is FIRST_VALUE(x). As you may guess, it returns the first value in the column x in the given order. Take a look:

SELECT
  Name,
  OpenDate,
  Budget,
  FIRST_VALUE(Budget) OVER(ORDER BY OpenDate ASC) AS FirstBudget
FROM Website;

In this query, we are still sorting rows by the opening date (ORDER BY OpenDate ASC), but we are showing the lowest budget rather than the earliest opening date (FIRST_VALUE(budget)). In this way, we can show the budget for the website that was opened first.

Note that this would be impossible to achieve with a simple MIN(...) function. MIN(budget) would simply show the smallest budget (500 in this case). That is not the same as the budget of the first website (3000)!

Exercise

Show the statistics for WebsiteId = 2. For each row, show the Day, the number of users, and the number of users on the first day ever. Name the column FirstUsers.

Stuck? Here's a hint!

Use FIRST_VALUE(Users) and sort by the day.