Calculate percent changes in contiguous ranges in PostgreSql I need to calculate price percent change in contiguous ranges. For example if price start moving up or down and I have sequence of decreasing or increasing values I need to grab first and last value of that sequence and calculate the change. 

I'm using window lag function to calculate direction, my problem- I can't generate unique RANK for the sequences to calculate percent changes.
I tired combination of RANK, ROW_NUMBER, etc. with no luck.

Here's my query 


WITH partitioned AS (
  SELECT
    *,
    lag(price, 1) over(ORDER BY time) AS lag_price

  FROM prices 
),
sequenced AS (
  SELECT
    *,
    CASE 
      WHEN price &gt; lag_price THEN 'up'
      WHEN price &lt; lag_price THEN 'down'
      ELSE 'equal'
    END
     AS direction
  FROM partitioned

),
ranked AS (
  SELECT
    *,
    -- Here's is the problem
    -- I need to calculate unique rnk value for specific sequence
    DENSE_RANK() OVER ( PARTITION BY direction ORDER BY time) + ROW_NUMBER() OVER ( ORDER BY time DESC) AS rnk
    -- DENSE_RANK() OVER ( PARTITION BY seq ORDER BY time),
    -- ROW_NUMBER() OVER ( ORDER BY seq, time DESC),
    -- ROW_NUMBER() OVER ( ORDER BY seq),
    -- RANK() OVER ( ORDER BY seq)

  FROM sequenced
),
changed AS (
  SELECT *,
    FIRST_VALUE(price) OVER(PARTITION BY rnk ) first_price,
    LAST_VALUE(price) OVER(PARTITION BY rnk ) last_price,
    (LAST_VALUE(price) OVER(PARTITION BY rnk ) / FIRST_VALUE(price) OVER(PARTITION BY rnk ) - 1) * 100 AS percent_change
    FROM ranked

)
SELECT
    *
FROM changed
ORDER BY time DESC;


and SQLFiddle with sample data
Let me know if this matches your desired result:

with ct1 as /* detecting direction: up, down, equal */
(
  select
      price, time,
      case 
        when lag(price) over (order by time) &lt; price then 'down'
        when lag(price) over (order by time) &gt; price then 'up'
        else 'equal' 
      end as dir
  from
      prices
)
, ct2 as /* setting reset points */
(
  select
      price, time,  dir,
      case 
          when coalesce(lag(dir) over (order by time), 'none') &lt;&gt; dir
          then 1 else 0
      end as rst
  from
      ct1
)
, ct3 as /* making groups */
(
  select
      price, time, dir,
      sum(rst) over (order by time) as grp
  from
      ct2
)
select /* calculates min, max price per group */
    price, time, dir,
    min(price) over (partition by grp) as min_price,
    max(price) over (partition by grp) as max_price
from
    ct3
order by
    time;




 price | time                | dir   | min_price | max_price
-----: | :------------------ | :---- | --------: | --------:
0.6550 | 2019-09-23 15:48:00 | equal |    0.6550 |    0.6550
0.6500 | 2019-09-23 15:49:00 | up    |    0.6500 |    0.6500
0.6550 | 2019-09-23 15:50:00 | down  |    0.6550 |    0.6550
0.6530 | 2019-09-23 15:51:00 | up    |    0.6410 |    0.6530
0.6410 | 2019-09-23 15:53:00 | up    |    0.6410 |    0.6530
0.6495 | 2019-09-23 15:55:00 | down  |    0.6495 |    0.6495
0.6450 | 2019-09-23 15:56:00 | up    |    0.6380 |    0.6450
0.6380 | 2019-09-23 15:59:00 | up    |    0.6380 |    0.6450
0.6430 | 2019-09-23 16:03:00 | down  |    0.6430 |    0.6430
0.6400 | 2019-09-23 16:06:00 | up    |    0.6400 |    0.6400
0.6550 | 2019-09-23 16:07:00 | down  |    0.6550 |    0.6550
0.6543 | 2019-09-23 16:08:00 | up    |    0.6543 |    0.6543
0.6550 | 2019-09-23 16:09:00 | down  |    0.6550 |    0.6560
0.6560 | 2019-09-23 16:10:00 | down  |    0.6550 |    0.6560
0.6550 | 2019-09-23 16:12:00 | up    |    0.6550 |    0.6550
0.6550 | 2019-09-23 16:13:00 | equal |    0.6550 |    0.6550
0.6550 | 2019-09-23 16:14:00 | equal |    0.6550 |    0.6550
0.6580 | 2019-09-23 16:15:00 | down  |    0.6580 |    0.6580
0.6580 | 2019-09-23 16:16:00 | equal |    0.6580 |    0.6580
0.6450 | 2019-09-23 16:21:00 | up    |    0.6450 |    0.6450
0.6450 | 2019-09-23 16:24:00 | equal |    0.6450 |    0.6450
0.6450 | 2019-09-23 16:27:00 | equal |    0.6450 |    0.6450
0.6450 | 2019-09-23 16:42:00 | equal |    0.6450 |    0.6450
0.6460 | 2019-09-23 16:51:00 | down  |    0.6460 |    0.6460
0.6400 | 2019-09-23 16:54:00 | up    |    0.6400 |    0.6400
0.6450 | 2019-09-23 17:09:00 | down  |    0.6450 |    0.6450
0.6400 | 2019-09-23 17:12:00 | up    |    0.6400 |    0.6400
0.6450 | 2019-09-23 17:15:00 | down  |    0.6450 |    0.6450
0.6450 | 2019-09-23 17:18:00 | equal |    0.6450 |    0.6450
0.6499 | 2019-09-23 17:39:00 | down  |    0.6499 |    0.6499
0.6499 | 2019-09-23 17:42:00 | equal |    0.6499 |    0.6499
0.6499 | 2019-09-23 17:45:00 | equal |    0.6499 |    0.6499
0.6450 | 2019-09-23 17:48:00 | up    |    0.6450 |    0.6450
0.6450 | 2019-09-23 17:51:00 | equal |    0.6450 |    0.6450
0.6401 | 2019-09-23 17:54:00 | up    |    0.6335 |    0.6401
0.6335 | 2019-09-23 17:57:00 | up    |    0.6335 |    0.6401
0.6336 | 2019-09-23 18:00:00 | down  |    0.6336 |    0.6550
0.6400 | 2019-09-23 18:12:00 | down  |    0.6336 |    0.6550
0.6410 | 2019-09-23 18:15:00 | down  |    0.6336 |    0.6550
0.6430 | 2019-09-23 18:39:00 | down  |    0.6336 |    0.6550
0.6440 | 2019-09-23 18:48:00 | down  |    0.6336 |    0.6550
0.6450 | 2019-09-23 18:54:00 | down  |    0.6336 |    0.6550
0.6526 | 2019-09-23 19:16:00 | down  |    0.6336 |    0.6550
0.6527 | 2019-09-23 19:19:00 | down  |    0.6336 |    0.6550
0.6550 | 2019-09-23 19:22:00 | down  |    0.6336 |    0.6550
0.6550 | 2019-09-23 19:23:00 | equal |    0.6550 |    0.6550
0.6526 | 2019-09-23 19:35:00 | up    |    0.6526 |    0.6526
0.6550 | 2019-09-23 19:36:00 | down  |    0.6550 |    0.6550
0.6550 | 2019-09-23 19:37:00 | equal |    0.6550 |    0.6550
0.6550 | 2019-09-23 19:40:00 | equal |    0.6550 |    0.6550



db&lt;>fiddle here