Select average difference between dates in SQL

Question

I have a transaction table where each entry is a transaction; one of the fields is a date field.

I want to select some info from the transaction table with a WHERE condition, and one of the things I want is the average difference between dates. Effectively I am trying to find the average frequency of transactions meeting my condition, i.e.

SELECT (average_date_difference) FROM transactions WHERE customerID = xyz;

Where, obviously, (average_date_difference) would be some SQL statement. So my question is, does SQL have a way to select this easily? Or will I need to implement some way to do it programatically? Thanks!

Example:

CustomerID | date

c1 | 25/11/2020

c1 | 21/11/2020

c1 | 18/11/2020

25 - 21 = 4
21 - 18 = 3
(3 + 4) / 2 = 3.5
(average_date_difference) = 3.5 days (expected output)

This answer is for MS SQL but should help you: https://stackoverflow.com/questions/9994862/date-difference-between-consecutive-rows — kmoser, Dec 02 '20 at 04:58
Sample data with expected output will help to understand your problem better — Arun Palanisamy, Dec 02 '20 at 05:28

score 1 · Answer 1 · answered Dec 02 '20 at 06:16

1

SELECT avg( datediff(date, lag(date) over (partition by customerID order by date)) )
FROM transactions
WHERE customerID = 'c1';

answered Dec 02 '20 at 06:16

mck

40,932
13
35
50

Select average difference between dates in SQL

1 Answers1