CODEWITHSUNDEEP
sqlamazon-redshift
rodrigocf
rodrigocf

Reputation: 2099

Minimum difference between dates in the same column in Redshift

I have data like this:

person_id   date1         
1           2016-08-03   
1           2016-08-04    
1           2016-08-07

What i want a as a result is the minimum difference between all dates for person_id, in this case the minimum difference is 1 day(between 8/3 and 8/4).

Is there a way to query for this grouped by person_id in redshift?

Thanks!

Upvotes: 0

Views: 443

Answers (3)

kjmerf
kjmerf

Reputation: 4345

This one uses a self join to compare each date:

SELECT t1.person_id, MIN(datediff(t1.date1, t2.date1)) AS difference
FROM t t1
INNER JOIN t t2
ON t1.person_id = t2.person_id
AND t1.date1 > t2.date1
GROUP by t1.person_id

Tested here: http://sqlfiddle.com/#!9/1638f/1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I assume you want this for each person. If so, use lag() or lead() and aggregation:

select person_id, min(next_date1 - date1)
from (select t.*,
             lead(date1) over (partition by person_id order by date1) as next_date1
      from t
     ) t
group by person_id;

Upvotes: 4

Pரதீப்
Pரதீப்

Reputation: 93754

SELF JOIN should work you. Try this way

SELECT a.date1 - b.date1 
FROM   table1 a 
       JOIN table1 b 
         ON a.person_id = b.person_id 
            AND a.date1 <> b.date1 
Where a.date1 - b.date1 > 0
ORDER  BY a.date1 - b.date1 ASC 
LIMIT  1

Upvotes: 1

Related Questions