Calculating AVG DIFF GROUPED timestamps in another field?

Question

I have a table "track_events" that tracks ping signals of beacons (regions) in the following format:

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

I am trying to build logic to evaluate if the beacon stopped generating data by defining [avg. the time difference between the triggers], and then the generation of a list of all regions where [avg. time differences btw triggers] <[current time is the last trigger]. My logic is this:

• use the LAG type f (x) to create the prev_triggered_at field;
• Find the AVG time difference between the "triggered_at" and "prev_triggered_at" fields;
• generates a list of areas in which this avg <[current time is the last ping]

Any suggestions for writing this query?

Answer 1

, , . . prev_triggered_at, AVG, Diff ..

1: script:

      select a.id, a.region, a.triggered_at
           , max(b.triggered_at) as  prev_triggered_at
      from track_events a 
      inner join track_events b 
      on a.region=b.region
      and a.triggered_at>b.triggered_at
      group by  a.id,a.region,a.triggered_at

2: AVG , avg(). , , , .

3: where, , .