Comment by forinti

A few cool tricks I use with window functions:

1- To find blocks of contiguous values, you can use something similar to Gauss' trick for calculating arithmetic progressions: sort them by descending order and add each value to the row number. All contiguous values will add to the same number. You can then apply max/min and get rows that correspond to the blocks of values.

    select min(n), max(n) from (
      select n, n+row_number() over (order by n desc) group
      from numbers
    )
    group by group
    order by 1

2- You can use a window function with exponential/logarithms in order to calculate the accumulated inflation for the last n months:

    select date, inflation, (exp(accumulated)-1)*100 from (
      select date, inflation, sum(ln(1+(inflation/100))) over (order by date desc rows between current row and 11 following) as accumulated
      from inflation
    ) 

3- You can do all the paging in SQL (fetch page n of m) or simply add a column with the total number of rows (this often makes it easier to process the results).