title: redshift subquery reference issue - self join and min
body: <p>I have a query that works well on a standard sql, but redshift has restrictions that doesn't allow for queries that reference outer query info within the inner select.</p>
  
  <p>The basic idea of the query is to produce diff for a timestamp from the last time this customer posted a response. I have customer post timestamps from a forum and I want to know when they post, what was the difference in time since the last time they posted. Here's the query running on test data in sql, with expected output: <a href="http://sqlfiddle.com/#!9/04358/2" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/04358/2</a></p>
  
  <p>Here's the query:</p>
  
  <pre><code>SELECT
    current.id,
    current.datetime,
    TIMESTAMPDIFF(minute, current.datetime, IFNULL(next.datetime, 
  current.datetime)) as diff
  FROM
    TestTable as current
  LEFT JOIN
    TestTable as next
      on next.datetime = (select min(datetime) from TestTable
        where datetime &gt; current.datetime and id = current.id)
  order by
    current.id, current.datetime
  </code></pre>
  
  <p>In redshift, this does not work since the subquery pattern is not supported. Any ideas how to turn this into a non-referential sub-query? </p>