Average time between two orders

Question

I have order data table for customers and want to find out avg. number of days required for a customer to make a second transaction, after making first, # days to move from 2nd to 3rd, so on and so forth. Example table:

User ID Rank Time
111     1    01/08/17
111     2    05/08/17
111     3    08/08/17
111     4    10/08/17
111     5    15/08/17
111     6    20/08/17
112     1    07/08/17
112     2    08/08/17
112     3    09/08/17
112     4    10/08/17

Answer 1

Take the first date, and then compare each date with the previous one:

set @d := (select date from yourTable where userid=111 and rank=1);

select rank, datediff( date, @d), @d:=date FROM yourTable;

You will get something like this:

1   0    2017-08-01
2   4    2017-08-05
3   3    2017-08-08
4   3    2017-08-11
5   6    2017-08-17

And then just make a small addition to your query:

set @d := (select date from yourTable where userid=111 and rank=1);

select avg(days) from 
   (select rank, datediff( date, @d) days, @d:=date FROM yourTable) td;

And the result would be:

avg(days)
3.2000