title: Calculating AVG DIFF btw timestamps GROUPED BY other field?
body: <p>I have a "track_events" table that tracks pings to beacons (regions) in the following format:</p>
  
  <pre><code>id  region  triggered_at
  1234  abc  2016-03-04 21:07:18.817+00
  1235  def  2016-03-04 22:04:11.817+00
  1236  abc  2016-03-05 01:21:43.817+00
  1237  def  2016-03-05 07:44:43.817+00
  1238  abc  2016-03-05 11:34:45.817+00
  1238  ghi  2016-03-05 14:09:55.817+00
  1238  abc  2016-03-06 02:12:10.817+00
  </code></pre>
  
  <p>I'm trying to build logic to estimate if a beacon (region) has stopped generating data by determining the [avg. time difference between triggers] and then generating a list of all regions where the [avg. time difference btw triggers] &lt; [current time - last trigger]. My logic is as follows: </p>
  
  <ol>
  <li>use a LAG type f(x) to generate a "prev_triggered_at" field; </li>
  <li>find the AVG time difference between "triggered_at" and "prev_triggered_at" fields;</li>
  <li>generate list of regions where this avg &lt; [current time - last ping]</li>
  </ol>
  
  <p>Any suggestions on how to write this query? </p>