CODEWITHSUNDEEP
sqlprestoamazon-athena
hinewwiner
hinewwiner

Reputation: 707

SQL - Given sequence of data, how do I query the origin?

Let's assume we have the following data.

|  UUID |       SEENTIME      |     LAST_SEENTIME    |
------------------------------------------------------
| UUID1 | 2020-11-10T05:00:00 |                      |
| UUID2 | 2020-11-10T05:01:00 |  2020-11-10T05:00:00 |
| UUID3 | 2020-11-10T05:03:00 |  2020-11-10T05:01:00 |
| UUID4 | 2020-11-10T05:04:00 |  2020-11-10T05:03:00 |
| UUID5 | 2020-11-10T05:07:00 |  2020-11-10T05:04:00 |
| UUID6 | 2020-11-10T05:08:00 |  2020-11-10T05:07:00 |

Each data is connected to each other via LAST_SEENTIME.

In such case, is there a way to use SQL to identify these connected events as one? I want to be able to calculate start and end to calculate the duration of this event.

Upvotes: 0

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You can use a recursive CTE. The exact syntax varies by database, but something like this:

with recursive cte as 
      select uuid as orig_uuid, uuid, seentime
      from t
      where last_seentime is null
      union all
      select cte.orig_uuid, t.uuid, t.seentime
      from cte join
           t
           on cte.seentime = t.last_seentime
    )
select orig_uuid,
       max(seentime) - min(seentime) -- or whatever your database uses
from cte
group by orig_uuid;

Upvotes: 2

Related Questions