title: Using window function in redshift to aggregate conditionally
body: <p>I have a table with following data:
  <a href="https://i.stack.imgur.com/jqdGi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jqdGi.png" alt="input data"></a></p>
  
  <p>Link to test data: <a href="http://sqlfiddle.com/#!15/dce01/1/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!15/dce01/1/0</a></p>
  
  <p>I want to aggregate the items column (using <code>listagg</code>) for each group in gid in sequence as specified by seq column based on the condition that aggregation ends when pid becomes <code>0</code> again for a group.</p>
  
  <p>i.e.
  for group g1, there would be 2 aggregations; 1 for seq 1-3 and another for sequence 4-6; since for group g1, the pid becomes 0 for seq 4.</p>
  
  <p>I expect the result for the given example to be as follows (<em>Please note that <code>seq</code> in result is the min value of seq for the group where the pid becomes 0</em>):
  <a href="https://i.stack.imgur.com/1BSeS.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1BSeS.png" alt="result"></a></p>