How to calculate AVG, MIN & MAX of date interval of a column in a table?

I have a table more like this

    +----+-------------+---------------------+
    | id |  order_id   | transaction_date    |
    +----+-------------+---------------------+
    | 1  | TEST000001  | 2018-01-01 00:00:10 |
    | 2  | TEST000002  | 2018-01-01 00:10:10 |
    | 3  | TEST000003  | 2018-01-02 03:18:01 |
    | 4  | TEST000004  | 2018-02-04 05:00:00 |
    | 5  | TEST000005  | 2018-02-10 16:00:10 |
    +----+-------------+---------------------+

There is datetime field named transaction_date, so what I want to do is doing a calculation of time interval (day interval), so that I will get an average, minimum and maximum time interval for all records I have.

Sample expectation :

    +------------------+------------------+------------------+
    | average_interval | minimum_interval | maximum_interval |
    +--------------------------------------------------------+
    |              2.0 |                1 |                3 |
    +------------------+------------------+------------------+

What I’ve done so far is using LAG() function more like the following SELECT script.

SELECT AVG(date_interval) AS average_interval,
       MIN(date_interval) AS minimum_interval,
       MAX(date_interval) AS maximum_interval
FROM (
  SELECT
    transaction_date - LAG(transaction_date)
      OVER (PARTITION BY transaction_date ORDER BY id) AS date_interval
    FROM transactions
) t;

But the outcome was far from my expectation, null result

I believe thats not a proper way to solve it, any suggestions ?

Answer

I am not confident about using - in MySQL. Use TIMESTAMPDIFF(). Your problem, however, is the PARTITION BY lcause:

SELECT AVG(date_interval) AS average_interval,
       MIN(date_interval) AS minimum_interval,
       MAX(date_interval) AS maximum_interval
FROM (SELECT TIMESTAMPDIFF(second, transaction_date,
                           LAG(transaction_date) OVER (ORDER BY transaction_date) 
                          ) AS date_interval
      FROM transactions
     ) t;