11. Window Functions.sql

-- WINDOW FUNCTONS

-- Window functions in SQL are speecial functions that perform calculations across a set of rows - but without collapsing them into a single output row (unlike aggregate functions)
-- They allow us to do things like ranking, running totals, moving average and comparisons between rows while still keeping all the rows in the result.


use ecom;

select * from dim_product;

-- Suppose we need to find average of unit_price

select 
	avg(unit_price)
from
	dim_product;
    
-- Now, requirement has changed. We should't squeeze the rows. We need the avg price as seperate column with value for every row.

select
	*,
    sum(unit_price) over (order by unit_price) as running_total -- Shows running total sorted by unit price
from
	dim_product;
    
select
	*,
    avg(unit_price) over (order by launch_date) as running_avg -- Finds how much we earned till launch_date on average
from
	dim_product;

-- But our requirement was not this.
-- FRAME CLAUSES

select
	*,
    sum(unit_price) over (order by launch_date rows between unbounded preceding and current row) as running_total
from
	dim_product;

-- The above query achieves same thing (running total) as we find before, using frames. Window functions apply function on rows. In last query, it took default frame.
-- unbounded preceeding : all the previous row
-- unbounded following : it consider following rows as well

select
	*,
    avg(unit_price) over (order by launch_date rows between unbounded preceding and unbounded following) as running_total
from
	dim_product;