how to find out time difference (on day) for every transaction on each users using mysql

Question

I have a table like this:

CREATE TABLE test (
  ID SERIAL PRIMARY KEY,
  user_id INT,
  createdAt DATE,
  status_id INT
);

INSERT INTO test VALUES
  (1, 12, '2020-01-01', 4),
  (2, 12, '2020-01-03', 7),
  (3, 12, '2020-01-06', 7),
  (4, 13, '2020-01-02', 5),
  (5, 13, '2020-01-03', 6),
  (6, 14, '2020-03-03', 8),
  (7, 13, '2020-03-04', 4),
  (8, 15, '2020-04-04', 7),
  (9, 14, '2020-03-02', 6),
  (10, 14, '2020-03-10', 5),
  (11, 13, '2020-04-10', 8);

this is my fiddle

In that table there's id for the id of each transaction, user_id was the user, createdAt was the date of transaction happen, and status_id was the status for every transaction (in this case status_id 4, 5, 6, 8 are approved transaction)

I want to find out max, min, avg different day for every each transaction on every users who doing transaction between '2020-02-01' and '2020-04-01' with >1 transaction approved on that period

This is my query:

SELECT MIN(diff) AS `MIN`, MAX(diff) AS `MAX`, SUM(diff) / COUNT(DISTINCT user_id) AS `AVG`
FROM (
  SELECT ID, user_id, DATEDIFF((SELECT t2.createdAt FROM test t2 WHERE t2.user_id = t1.user_id AND t1.createdAt <= t2.createdAt AND t2.id <> t1.id LIMIT 1), t1.createdAt) AS diff
  FROM test t1
  where 
  status_id in (4, 5, 6, 8)
  HAVING SUM(t1.user_id BETWEEN '2020-02-01' AND '2020-04-01')
                  AND SUM(t1.user_id >= '2020-02-01') > 1 

) DiffTable
WHERE diff IS NOT NULL

but it's said:

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'fiddle_KDQIQDMUZEIOVXFHRZPY.t1.ID'; this is incompatible with sql_mode=only_full_group_by

what should i do?

this is my fiddle

expected result

+-----+-----+---------+
| MAX | MIN | AVERAGE |
+-----+-----+---------+
|  36 |   1 |      22 |
+-----+-----+---------+

explanation :

- the user_id who have approval transaction on 2020-02-01 until 2020-04-01 and user_id who have transaction more than 1 are user_id 13 & 14
- the maximum of different day on 2020-02-01 until 2020-04-01 are user_id 13 which the different day for each transaction happen in 2020-03-04 and doing next transaction again in 2020-04-10
- the minimum day of different day of each transaction are user_id 14 who doing transaction on 2020-03-02 and next transaction 2020-03-03
- average are 22 days (sum of different day on user_Id 13 & 14 / amount of user_id who fit on this condition) 

Answer 1

You need to do the GROUPing outside of your subquery; the subquery should just be used to limit the selected transactions to those which have the desired status_id value and dates within the required range. Then you can select users with more than one transaction in the period in the outer query:

SELECT user_id,
       COUNT(*) AS transactions, 
       MIN(diff) AS `MIN`, 
       MAX(diff) AS `MAX`, 
       SUM(diff) / COUNT(diff) AS `AVG`
FROM (
  SELECT user_id, DATEDIFF((SELECT MIN(t2.createdAt)
                            FROM test t2
                            WHERE t2.user_id = t1.user_id
                              AND t1.createdAt < t2.createdAt
                              AND t2.status_id in (4, 5, 6, 8)
                            ), t1.createdAt) AS diff
  FROM test t1
  WHERE status_id in (4, 5, 6, 8)
    AND createdAt BETWEEN '2020-02-01' AND '2020-04-01'
) DiffTable
WHERE diff IS NOT NULL
GROUP BY user_id
HAVING COUNT(*) > 1

Output (for your fiddle):

user_id     transactions    MIN     MAX     AVG
14          2               1       7       4.0000

Demo on dbfiddle

If you want the values based on all transactions that occurred over that period, rather than by user_id, you can simply remove the GROUP BY and HAVING clauses:

SELECT COUNT(*) AS transactions, 
       MIN(diff) AS `MIN`, 
       MAX(diff) AS `MAX`, 
       SUM(diff) / COUNT(diff) AS `AVG`
FROM (
  SELECT user_id, DATEDIFF((SELECT MIN(t2.createdAt)
                            FROM test t2
                            WHERE t2.user_id = t1.user_id
                              AND t1.createdAt < t2.createdAt
                              AND t2.status_id in (4, 5, 6, 8)
                            ), t1.createdAt) AS diff
  FROM test t1
  WHERE status_id in (4, 5, 6, 8)
    AND createdAt BETWEEN '2020-02-01' AND '2020-04-01'
) DiffTable
WHERE diff IS NOT NULL

Output:

transactions    MIN     MAX     AVG
3               1       37      15.0000

Demo on dbfiddle

Note that there a couple of issues with the existing subquery in the DATEDIFF computation: a LIMIT without ORDER BY is not guaranteed to give the expected results, and there is no conditioning on status_id. I've fixed both those issues in my updated queries.