Bikas Katwal
Bikas Katwal

Reputation: 2035

Query to collapse records if record is substring and falls in a time interval

I have the below events data:

events table

event_id | timestamp         | label
--------------------------------------------------
1        |2021-11-15 10:23:54| so
1        |2021-11-15 10:24:30| software
2        |2021-11-15 10:24:54| software Engineer
3        |2021-11-15 10:23:54| test
3        |2021-11-15 10:24:31| test eng
3        |2021-11-15 10:24:54| test Engineer
2        |2021-11-16 10:26:54| software Engineer and Test

I want to collapse this result set to the below result set.

Result:

event_id | timestamp         | label
----------------------------------------------------------
2        |2021-11-15 10:24:54| software Engineer
3        |2021-11-15 10:24:31| test Engineer
2        |2021-11-16 10:26:54| software Engineer and Test

Basically, I want to remove all the records that are substring of any records in future timestamp and have occurred in less than 3 mins interval.

In the above example: so and software is removed because software Engineer is seen in the later timestamp, which is within 3 minutes interval.

event_id isn't necessarily always same.

Is this something possible using a query?

I know that it is easy to do using a program/script. Still, I would like to know how this can be done using a query.

Upvotes: 0

Views: 38

Answers (0)

Related Questions