CODEWITHSUNDEEP
mysql
user1893354
user1893354

Reputation: 5938

Difficult MySQL Query - Getting Max difference between dates

I have a MySQL table of the following form

account_id | call_date
1            2013-06-07
1            2013-06-09  
1            2013-06-21
2            2012-05-01
2            2012-05-02
2            2012-05-06

I want to write a MySQL query that will get the maximum difference (in days) between successive dates in call_date for each account_id. So for the above example, the result of this query would be

account_id | max_diff
1            12
2            4

I'm not sure how to do this. Is this even possible to do in a MySQL query?

I can do datediff(max(call_date),min(call_date)) but this would ignore dates in between the first and last call dates. I need some way of getting the datediff() between each successive call_date for each account_id, then finding the maximum of those.

Upvotes: 1

Views: 2654

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you have an index on account_id, call_date, then you can do this rather efficiently without variables:

select account_id, max(call_date - prev_call_date) as diff
from (select t.*,
             (select t2.call_date
              from table t2
              where t2.account_id = t.account_id and t2.call_date < t.call_date
              order by t2.call_date desc
              limit 1
             ) as prev_call_date
      from table t
     ) t
group by account_id;

Upvotes: 1

Majid Laissi
Majid Laissi

Reputation: 19788

SELECT a1.account_id , max(a1.call_date - a2.call_date)
FROM account a2, account a1
WHERE a1.account_id = a2.account_id
AND a1.call_date > a2.call_date
AND NOT EXISTS
    (SELECT 1 FROM account a3 WHERE a1.call_date > a3.call_date AND a2.call_date < a3.call_date)
GROUP BY a1.account_id

Which gives :

ACCOUNT_ID  MAX(A1.CALL_DATE - A2.CALL_DATE)
1           12
2           4

Upvotes: 0

Marcus Adams
Marcus Adams

Reputation: 53830

Just for educational purposes, doing it with JOIN:

SELECT t1.account_id,
  MAX(DATEDIFF(t2.call_date, t1.call_date)) AS max_diff
FROM t t1
LEFT JOIN t t2
ON t2.account_id = t1.account_id
  AND t2.call_date > t1.call_date
LEFT JOIN t t3
ON t3.account_id = t1.account_id
  AND t3.call_date > t1.call_date
  AND t3.call_date < t2.call_date
WHERE t3.account_id IS NULL
GROUP BY t1.account_id

Since you didn't specify, this shows max_diff of NULL for accounts with only 1 call.

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

I'm sure fp's answer will be faster, but just for fun...

SELECT account_id
     , MAX(diff) max_diff
  FROM 
     ( SELECT x.account_id
            , DATEDIFF(MIN(y.call_date),x.call_date) diff
         FROM my_table x 
         JOIN my_table y 
           ON y.account_id = x.account_id 
          AND y.call_date > x.call_date 
        GROUP 
           BY x.account_id
            , x.call_date
     ) z
 GROUP
    BY account_id;

Upvotes: 4

fancyPants
fancyPants

Reputation: 51888

CREATE TABLE t
    (`account_id` int, `call_date` date)
;

INSERT INTO t
    (`account_id`, `call_date`)
VALUES
    (1, '2013-06-07'),
    (1, '2013-06-09'),
    (1, '2013-06-21'),
    (2, '2012-05-01'),
    (2, '2012-05-02'),
    (2, '2012-05-06')
;

select account_id, max(diff) from (
select
account_id,
timestampdiff(day, coalesce(@prev, call_date), call_date) diff,
@prev := call_date
from
t
, (select @prev:=null) v
order by account_id, call_date
) sq 
group by account_id

| ACCOUNT_ID | MAX(DIFF) |
|------------|-----------|
|          1 |        12 |
|          2 |         4 |

Upvotes: 1

Related Questions