postgres tutorial 9 row_number, rank, first_value, last_value, lag, lead

SELECT
 product_name,
 price,
 group_name,
 AVG (price) OVER (PARTITION BY group_name)
FROM
 products
INNER JOIN product_groups USING (group_id);

------------------------------
ROW_NUMBER() function assigns a running serial number to rows in each partition.

SELECT
 product_name,
 group_name,
 price,
 ROW_NUMBER () OVER (
 PARTITION BY group_name
 ORDER BY
 price
 )
FROM
 products
INNER JOIN product_groups USING (group_id);

-----------------------------------
The RANK() function assigns ranking within an ordered partition.  If the values of the two rows are the same, the  RANK() function assigns the same rank, with the next ranking(s) skipped.

SELECT
 product_name,
 group_name,
  price,
 RANK () OVER (
 PARTITION BY group_name
 ORDER BY
 price
 )
FROM
 products
INNER JOIN product_groups USING (group_id);

------------------------------
SELECT
 product_name,
 group_name,
 price,
 FIRST_VALUE (price) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS lowest_price_per_group
FROM
 products
INNER JOIN product_groups USING (group_id);

----------------------------------
SELECT
 product_name,
 group_name,
 price,
 LAST_VALUE (price) OVER (
 PARTITION BY group_name
 ORDER BY
 price RANGE BETWEEN UNBOUNDED PRECEDING
 AND UNBOUNDED FOLLOWING
 ) AS highest_price_per_group
FROM
 products
INNER JOIN product_groups USING (group_id);

-----------------------------
LAG() function to return the prices from the previous row

SELECT
 product_name,
 group_name,
 price,
 LAG (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS prev_price,
 price - LAG (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS cur_prev_diff
FROM
 products
INNER JOIN product_groups USING (group_id);

--------------------------------
LEAD() function to return the prices from the next row

SELECT
 product_name,
 group_name,
 price,
 LEAD (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS next_price,
 price - LEAD (price, 1) OVER (
 PARTITION BY group_name
 ORDER BY
 price
 ) AS cur_next_diff
FROM
 products
INNER JOIN product_groups USING (group_id);