Windowing Functions

Windowing Functions:

In cases such as
1. From the current row to the end of the partition
2. For the 30 days preceeding the transaction date
3. All rows having a transaction amount within 5% of the current row's transaction amount.

-- COMPLETE PARTITION WINDOW

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- ROLLING CALCULATIONS
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- WINDOW OF (CURRENT ROW - X ROWS) , (CURRENT ROW) , (CURRENT ROW + Y ROWS)
ROWS BETWEEN X PRECEDING AND Y FOLLOWING



FIRST_VALUE  AND LAST_VALUE :

These can be used with the windowing functions to identify the values of the first and last values
in the window.

FIRST_VALUE (sum(total_sales)) over (order by month
                                     rows between 1 preceding and 1 following) first_value_in_window,
LAST_VALUE  (sum(total_sales)) over (order by month
                                     rows between 1 preceding and 1 following) last_value_in_window,
Avg(sum(total_sales)) over (order by month
                            rows between 1 preceding and 1 following) rolling_3_month_avg