
based on the window function definition. Using the Rank() function will result
in the same value for ties / equal values and the next rank value skipped. The
Dense_Rank() function behaves similarly except the next consecutive number is
used after a tie rather than skipped. The Row_Number() function will provide a
unique ordering value. The example query shows the Rank() function being
applied to rank sales reps by the number of opportunities for Q4 2016.

select owner, 
wonCount, 
rank() over (order by wonCount desc) rank 
from (
 select owner,
 count(*) wonCount
 from opportunities
 where stageName='ClosedWon'
 and closeDate >= '2016-10-01' and closeDate < '2016-12-31'
 group by owner
) t
order by rank;

with example results (note the query is technically incorrect by using
closeDate < '2016-12-31' however this creates a tie scenario for illustrative
purposes):

+----------------------+----------------------------------+------------------+
| owner                | wonCount                         | rank             |
+----------------------+----------------------------------+------------------+
| Bill                 | 19                               | 1                |
+----------------------+----------------------------------+------------------+
| Chris                | 15                               | 2                |
+----------------------+----------------------------------+------------------+
| 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   |
|              |           |                   |              |             |
+--------------+-----------+-------------------+--------------+-------------+