Average Time Difference Between Rows
Suppose you had sales table with timestamp and customer_id as the columns.
|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