Average Time Difference Between Rows

Suppose you had sales table with timestamp and customer_id as the columns.

id	timestamp	customer_id
1	2011-03-17 10:31:01	7
2	2011-03-17 10:45:05	8
3	2011-03-17 10:47:05	7
4	2011-03-17 10:50:10	8

You want to query for what’s the average time between purchases for each customer.

You can use:

SELECT t.customer_id,
       AVG(t.difference) as interval
FROM ( 
  SELECT customer_id,
         timestamp - lag(timestamp) OVER (PARTITION BY customer_id ORDER BY timestamp) AS difference
  FROM sales
) t
GROUP BY t.customer_id

You’ll get:

customer_id	interval
7	00:16:04
8	00:05:05

Sources:

• StackOverflow
• Postgres LAG() function
• SQL PARTITION BY