title: Athena (Presto) SQL window functions
body: <p>say I have the following data in Athena:  </p>
  
  <pre><code>id      ts
  uid1    1499672134268
  uid1    1499672134292
  uid1    1499672136189
  uid1    1499672136212
  uid1    1499719927907
  uid1    1499719927940
  uid1    1499719927975
  uid1    1499719927999
  uid2    1499670000000
  uid2    1499670000010
  uid2    1499688880010
  </code></pre>
  
  <p>I want to rank it so:</p>
  
  <pre><code>rank    id      ts
  1       uid1    1499672134268
  1       uid1    1499672134292
  1       uid1    1499672136189
  1       uid1    1499672136212
  2       uid1    1499719927907
  2       uid1    1499719927940
  2       uid1    1499719927975
  2       uid1    1499719927999
  1       uid2    1499670000000
  1       uid2    1499670000010
  2       uid2    1499688880010
  </code></pre>
  
  <p>the logic is:<br>
  the difference between the min value in the second group and the max value in the first group is larger than <code>x</code></p>
  
  <p>I am thinking some combination of <code>dense_rank() OVER (PARTITION BY id ... )</code> could resolve it, but I am a total newbie to window functions in SQL.</p>
  
  <p>Thanks</p>