-------+----------------------------------+------------------+
| Maria                | 14                               | 3                |
+----------------------+----------------------------------+------------------+
| Bob                  | 14                               | 3                |
+----------------------+----------------------------------+------------------+
| Olivier              | 10                               | 5                |
+----------------------+----------------------------------+------------------+

If the dense_rank function is used the rank values would be 1,2,3,3,4 and for
the row_number function the values would be 1,2,3,4,5.

First and Last Values
---------------------

The first_value and last_value functions allow determining the first and last
values of a given range. Combined with a group by this allows summarizing
opening and closing values. The example shows a more complex case where
detailed information is presented for first and last opportunity by quarter.

select a.year, 
a.quarter, 
f.accountName firstAccountName, 
f.owner firstOwner, 
f.amount firstAmount, 
l.accountName lastAccountName, 
l.owner lastOwner, 
l.amount lastAmount 
from (
 select year,
 quarter,
 min(firstId) firstId,
 min(lastId) lastId
 from (
  select year(closeDate) year,
  quarter(closeDate) quarter,
  first_value(id) over (partition by year(closeDate), quarter(closeDate)
order by closeDate rows between unbounded preceding and current row) firstId, 
  last_value(id) over (partition by year(closeDate), quarter(closeDate)
order by closeDate rows between current row and unbounded following) lastId 
  from opportunities  where stageName='ClosedWon'
 ) t
 group by year, quarter order by year,quarter
) a 
join opportunities f on a.firstId = f.id 
join opportunities l on a.lastId = l.id 
order by year, quarter;

with example results:

+----+------+------------+--------+---------+-----------+-------+--------+
| ye | quar | firstAccou | firstO | firstAm | lastAccou | lastO | lastAm |
| r  | er   | tName      | ner    | unt     | tName     | ner   | unt    |
+----+------+------------+--------+---------+-----------+-------+--------+
| 20 | 3    | Skidoo     | Bill   | 523295. | Skipstorm | Bill  | 151420 |
| 6  |      |            |        | 7       |           |       | 86     |
+----+------+------------+--------+---------+-----------+-------+--------+
| 20 | 4    | Skimia     | Chris  | 961513. | Avamm     | Maria | 112493 |
| 6  |      |            |        | 9       |           |       | 65     |
+----+------+------------+--------+---------+-----------+-------+--------+
| 20 | 1    | Yombu      | Bob    | 536875. | Skaboo    | Chris | 270273 |
| 7  |      |            |        | 1       |           |       | 08     |
+----+------+------------+--------+---------+-----------+-------+--------+

Prior and Next Example
----------------------

Sometimes it useful to understand the previous and next values in the context
of a given row. The lag and lead window functions provide this capability. By
default the offset is one providing the prior or next value but can also be
provided to get a larger offset. The example query is a report of
opportunities by account name showing the opportunity amount, and the prior
and next opportunity amount for that account by close date.

select accountName, 
closeDate,  
amount currentOppAmount, 
lag(amount) over (partition by accountName order by closeDate) priorAmount,
lead(amount) over (partition by accountName order by closeDate) nextAmount 
from opportunities 
order by accountName, closeDate 
limit 9;

with example results:

+--------------+-----------+-------------------+--------------+-------------+
| accountName  | closeDate | currentOppAmount  | priorAmount  | nextAmount  |
+--------------+-----------+-------------------+--------------+-------------+
| Abata        | 2016-09-1 | 645098.45         | NULL         | 161086.82   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-----ö’_›