Calculate avg purchase date gaps for repeated customer

[elaineng]

I have a simple table, which I would like to calculate the avg gap for each customer.

For CustomerID 1, year of purchased is 2020, 1999, 1997.
The gaps purchased is:
1 (2000-1999)
2 (1999-1997) 

Avg (1+2) = 1.5

For Customer 4, year of purchased is 2020, 1995, 1988

The gaps of purchase is:

25 (2020 - 1995)

7 (1995 - 1988)

Avg (25+7) = 16

How should the script like to achieve the above calculation?

Sample Table

[CustomerRecords]:

LOAD * INLINE [
CustomerID, PurchaseDate
1, 1/1/2020
2, 1/1/2020
3, 1/1/2020
4, 1/1/2020
5, 1/1/2020
1, 1/1/1999
2, 1/1/1998
3, 1/1/1990
4, 1/1/1995
1, 1/1/1997
4, 1/1/1988
];

[Arthur_Fong · Accepted solution]

Your scenario given for CustomerID 1 is 2000, but sample data shows 2020.

I used the sample data given and the result shows:

Script:

[CustomerRecords]:
LOAD CustomerID, PurchaseDate,Year(dATE#(PurchaseDate,'DD/MM/YYYY'))as Year INLINE [
CustomerID, PurchaseDate
1, 1/1/2020
2, 1/1/2020
3, 1/1/2020
4, 1/1/2020
5, 1/1/2020
1, 1/1/1999
2, 1/1/1998
3, 1/1/1990
4, 1/1/1995
1, 1/1/1997
4, 1/1/1988
];
NoConcatenate
tempData:

load *,if(RowNo()=1,null(),if(peek(CustomerID)=CustomerID,peek(Year)-Year,null())) as YearDiff resident CustomerRecords
order by CustomerID,PurchaseDate desc
;

Avg:
load CustomerID,avg(YearDiff) as AvgDiff
resident tempData
group by CustomerID
;
drop table CustomerRecords;