window functions in postgresql code example

Example 1: postgresql partition group by

SELECT 
    product_id,
    product_name,
	group_id,
    price,
    FIRST_VALUE(product_name) 
    OVER(
	PARTITION BY group_id
        ORDER BY price
        RANGE BETWEEN 
            UNBOUNDED PRECEDING AND 
            UNBOUNDED FOLLOWING
    ) lowest_price
FROM 
    products;

Example 2: windows aggregate functions in postgresql

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;