Finding average of multiple delta times I'm looking to find the average interval between successive rows across the whole query - delta-previous in below example:

SELECT *,
  "RecognitionTimestamp" - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
FROM "Detection" 
WHERE
  "RecognitionTimestamp" &gt;= '2018-10-03 09:01:00' AND 
  "RecognitionTimestamp" &lt;= '2018-10-04 09:00:00'


How can I do this?
I've tried avg("delta_previous"), avg(delta_previous),avg("RecognitionTimestamp" - lag("RecognitionTimestamp"))
It's not possible to run aggregate function over an expression involving a window function in the same query level, because aggregate functions are evaluated before window functions. You'd need a subquery like:

SELECT avg(delta_previous) AS avg_delta_previous
FROM (
   SELECT "RecognitionTimestamp"
        - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
   FROM   "Detection" 
   WHERE  "RecognitionTimestamp" &gt;= '2018-10-03 09:01:00'
   AND    "RecognitionTimestamp" &lt;  '2018-10-04 09:00:00'  -- typically, you'd want "&lt;" here, not "&lt;="!
   ) sub;


Consider the sequence of events in a SELECT query:


Best way to get result count before LIMIT was applied


But this simpler query should give you the same result, only much cheaper:

SELECT (max("RecognitionTimestamp")
      - min("RecognitionTimestamp")) / (count(*) - 1) AS avg_delta_previous
FROM   "Detection" 
WHERE  "RecognitionTimestamp" &gt;= '2018-10-03 09:01:00'
AND    "RecognitionTimestamp" &lt;  '2018-10-04 09:00:00';


Subtract the minimum from the maximum timestamp to get the total interval and divide it by the number or rows - 1. Voilá.