title: Recursive CTE in Amazon Redshift
body: <p>We are trying to port a code to run on Amazon Redshift, but Refshift won't run the recursive CTE function. Any good soul that knows how to port this?</p>
  
  <pre><code>with tt as (
        select t.*, row_number() over (partition by id order by time) as seqnum
        from t
       ),     
       recursive cte as (
        select t.*, time as grp_start
        from tt
        where seqnum = 1
        union all
        select tt.*,
               (case when tt.time &lt; cte.grp_start + interval '3 second'
                     then tt.time
                     else tt.grp_start
                 end)
        from cte join
             tt
             on tt.seqnum = cte.seqnum + 1
       )
  select cte.*,
         (case when grp_start = lag(grp_start) over (partition by id order by time)
               then 0 else 1
          end) as isValid
  from cte;
  </code></pre>
  
  <p>Or, a different code to reproduce the logic below.</p>
  
  <ul>
  <li>It is a binary result that:</li>
  <li>it is 1 if it is the first known value of an ID</li>
  <li>it is 1 if it is 3 seconds or later than the previous "1" of that ID</li>
  <li>It is 0 if it is less than 3 seconds than the previous "1" of that ID</li>
  </ul>
  
  <p>Note 1: this is not the difference in seconds from the previous record<br>
  Note 2: there are many IDs in the data set<br>
  Note 3: original dataset has ID and Date  </p>
  
  <p>Desired output:
  <a href="https://i.stack.imgur.com/k4KUQ.png" rel="nofollow noreferrer">https://i.stack.imgur.com/k4KUQ.png</a></p>
  
  <p>Dataset poc:
  <a href="http://www.sqlfiddle.com/#!15/41d4b" rel="nofollow noreferrer">http://www.sqlfiddle.com/#!15/41d4b</a></p>