how to count timediff for each users mysql

Question

let's say i have the data table like this

ID  users_Id   createdAt
1   12         '2020-01-01'
2   12         '2020-01-03'
3   12         '2020-01-06'
4   13         '2020-01-02'
5   13         '2020-01-03'

how do i get the timediff for every transaction and every users so the results are just like this

MAX   MIN   AVERAGE    MEDIAN
3     1     3          3

explanation:

the maximum of timediff happen in users_id 12 when '2020-01-03' until '2020-01-06' (3 days)
the mininum of timediff happen in users_id 13 when transaction between '2020-01-02' and '2020-01-03'
the average are 3 (2 days in users_Id 12 + 3 days in users_Id 12 + 1 days in users_Id 13)/count of the users_id (12 and 13)

Add your code which you try for expected result and what's you got. — Tushar, Mar 06 '20 at 10:41
this timediff are difficult for me sir, i always had a example of timediff for every row, not for every id's so im lil bit confused in here @Tushar — 18Man, Mar 06 '20 at 10:51
Do you really need the median? It is much more complicated to compute than the other columns. — GMB, Mar 06 '20 at 10:52

Sebastian Brosch · Accepted Answer · 2020-03-06T11:15:48.460

You can use something like this (without computing the median):

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 order_status_id in (4, 5, 6, 8)
) DiffTable
WHERE diff IS NOT NULL

The median is much more complicated to compute on MySQL. But you can use something like this based on this answer on StackOverflow. As you can see the query get very messy. There is no function like SUM or AVG on MySQL to get the median.

SELECT MIN(DiffTable.diff) AS `MIN`, MAX(DiffTable.diff) AS `MAX`, SUM(DiffTable.diff) / COUNT(DISTINCT user_id) AS `AVG`, MIN(median.diff) AS `MEDIAN`
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 order_status_id in (4, 5, 6, 8)
) DiffTable, (
  SELECT m1.diff 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 order_status_id in (4, 5, 6, 8)
  ) m1, (
    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 order_status_id in (4, 5, 6, 8)
  ) m2
  WHERE m1.diff IS NOT NULL AND m2.diff IS NOT NULL
  GROUP BY m1.diff
  HAVING SUM(SIGN(1-SIGN(m1.diff-m2.diff))) = (COUNT(*)+1)/2
) median
WHERE DiffTable.diff IS NOT NULL

demo on dbfiddle.uk

thankyou very much sir, i almost forgot sir, i forgot to add coloumn order_status_id, so it will counted if order_status_id in (4, 5, 6, 8). where do i can add order_status_id in (4, 5, 6, 8), is it after "from test t1" or after "where diff is not null?" — 18Man, Mar 06 '20 at 11:06

GMB · Answer 2 · 2020-03-06T11:05:11.393

1

In MySQL < 5.7, I would use a correlated subquery to recover the last created_at of the same user. This gives you all columns that you expect excepted the median:

select
    max(diff) max_diff,
    min(diff) min_diff,
    avg(diff) avg_diff
from (
    select
        t.*,
        datediff(
            created_at, 
            (select max(t1.created_at) from mytable t1 where t1.user_id = t.user_id and t1.created_at < t.created_at) 
        ) diff
    from mytable t
) t

edited Mar 06 '20 at 11:05

answered Mar 06 '20 at 10:54

GMB

188,822
23
52
100

thankyou sir, but i think this query lil bit wrong because you dont define that t1 table, and mysql cant read that – 18Man Mar 06 '20 at 11:03
thankyou very much sir, i almost forgot sir, i forgot to add coloumn order_status_id, so it will counted if order_status_id in (4, 5, 6, 8). where do i can add order_status_id in (4, 5, 6, 8)? is it after "t1.createdAt < t.createdAt" or after "from mytable t"? – 18Man Mar 06 '20 at 11:07

how to count timediff for each users mysql

2 Answers2

Linked