AVG interval length for table with purchases

Question

I would appreciate help with a table that is looking like this:

  client_id | transaction_date | amount
  -------------------------------------
      123   |     2017-01-16   |   12
      234   |     2017-01-11   |   25 
      345   |     2017-01-12   |  211

I want to get the average for a period of time that the same client did next transaction - in other words - what was the avg number of days that led to the next transaction for certain user?

I was thinking of some TIMESTAMPDIFF, but no idea how to implement that in this case.

What MySQL version are you running/willing to run? Recursive CTEs in MySQL-8.0/MariaDB-10.2 make these sort of queries easier. — danblack, Sep 19 '18 at 23:44

score 0 · Answer 1 · answered Sep 19 '18 at 23:46

Do it something like this

SELECT client_id,avg(DATEDIFF(transaction_date,next_transaction_date)) FROM
(
    SELECT 
    client_id,
    transaction_date ,
    LEAD(transaction_date,1) OVER (
        PARTITION BY client_id
        ORDER BY transaction_date) next_transaction_date
    FROM 
    table    
) as s WHERE next_transaction_date is not null

Here is reference link for the understanding of LEAD function

http://www.mysqltutorial.org/mysql-window-functions/mysql-lead-function/

score 0 · Accepted Answer · answered Sep 20 '18 at 01:56

The simplest method is to take the difference between maximum and minimum and divide by one less than the number of rows.

So:

select client_id,
       ( datediff(max(transaction_date), min(transaction_date)) /
         nullif(count(*) - 1, 0)
       ) as avg_duration
from t
group by client_id;

Because your are dealing with dates, you can use datediff() rather than timestampdiff() (saves you on a function argument).

AVG interval length for table with purchases

2 Answers2