title: How to find peak and valley in running sequence in sql
body: <p>So I've got a dataset in athena, so for the purposes of this you can probably treat it as a postgres database. The a sample of the data can be seen in this <a href="http://www.sqlfiddle.com/#!17/4b3732/2" rel="nofollow noreferrer">sql fiddle</a>.</p>
  
  <p>Here's a sample:</p>
  
  <pre><code>  create table vals (
    timestamp int,
    type varchar(25),
    val int
    );
  
    insert into vals(timestamp,type, val) 
    values      (10, null, 1),
                (20, null, 2),
                (39, null, 1),
                (40,'p',1),
                (50,'p',2),
                (60,'p',1),
                (70,'v',5),
                (80,'v',6),
                (90,'v',6),
                (100,'v',3),
                (110,null,3),
                (120,'v',6),
                (130,null,3),
                (140,'p',10),
                (150,'p',8),
                (160,null,3),
                (170,'p',1),
                (180,'p',2),
                (190,'p',2),
                (200,'p',1),
                (210,null,3),
                (220,'v',1),
                (230,'v',1),
                (240,'v',3),
                (250,'v',41)               
  </code></pre>
  
  <p>What I'd like to get is a dataset that includes all the values but highlights the highest value of the 'p' and the lowest value of the contiguous 'v's.</p>
  
  <p>so ultimately I'd get:</p>
  
  <pre><code>   timestamp, type, value, is_peak
      (10, null, 1, null),
      (20, null, 2, null),
      (39, null, 1, null),
      (40,'p',1, null),
      (50,'p',2, 1),
      (60,'p',1, null),
      (70,'v',5, null),
      (80,'v',6, null),
      (90,'v',6, null),
      (100,'v',3, 1),
      (110,null,3, null),
      (120,'v',6, 1),
      (130,null,3, null),
      (140,'p',10, 1),
      (150,'p',8, null),
      (160,null,3, null),
      (170,'p',1, null),
      (180,'p',2, 1),
      (190,'p',2, null), -- either this record or 180 would be fine
      (200,'p',1, null),
      (210,null,3, null),
      (220,'v',1, 1), -- again either this or 230
      (230,'v',1, null),
      (240,'v',3, null),
      (250,'v',41, null) 
  </code></pre>
  
  <p>is peak has a lot of choices for the type, if it were some sort of denserank or incrementing number that'd be fine. Just so I can be confident that within the contiguous set the 'marked' one is the highest or lowest value.</p>
  
  <p>Good luck appreciate the assist</p>
  
  <p>note: The peak of the max of the peaks or min of the valley's can be anwhere in the contiguous set, but as soon as the type changes we start over.</p>