Rewrite query to use Analytic Functions

This looks like a good fit for SQL pattern matching:

select * from eventsmatch_recognize (  partition by event_id  order by event_change_date  measures     count ( ins.* ) ins_count,    min ( event_change_date ) dt  pattern ( ins upd* del )  define     ins as event_trigger = 'I' and event_location = 'LOC1',    upd as event_trigger = 'U',    del as event_trigger = 'D' and event_location = 'LOC7');INS_COUNT    DT                                1 16-MAR-2020 12:33:58 

This searches for an I(nserts) at LOC1 followed by a D(elete) at LOC7, with any number of U(pdates) in-between.

Using classic analytic function only.

Filter only the relevant events

(EVENT_TRIGGER = 'I' AND EVENT_LOCATION = 'LOC1')  OR  -- only LOC1 inserts EVENT_TRIGGER = 'D')                                  -- all deletes

Then LEAD the next Delete and check the location

with evnt as(  select EVENT_ID, EVENT_LOCATION, EVENT_TRIGGER, EVENT_CHANGE_DATE,    lead(EVENT_TRIGGER) over (PARTITION BY EVENT_ID                                   order by EVENT_CHANGE_DATE, EVENT_LOCATION)      as EVENT_TRIGGER_LEAD,    lead(EVENT_LOCATION) over (PARTITION BY EVENT_ID                                   order by EVENT_CHANGE_DATE, EVENT_LOCATION)      as EVENT_LOCATION_LEAD  from EVENTS  where (EVENT_TRIGGER = 'I' AND EVENT_LOCATION = 'LOC1') OR EVENT_TRIGGER = 'D')select   EVENT_ID, EVENT_LOCATION, EVENT_TRIGGER, EVENT_CHANGE_DATE,  EVENT_TRIGGER_LEAD, EVENT_LOCATION_LEADfrom evntwhere EVENT_TRIGGER = 'I'  and EVENT_TRIGGER_LEAD = 'D'   and EVENT_LOCATION_LEAD = 'LOC7'order by EVENT_ID, EVENT_CHANGE_DATE, EVENT_LOCATION;

You can use the analytical function SUM to add the 1 to result when it is LOC1 and I and add -1 when it is D, then the final result would be the record having sum = 0 and location as LOC7.

see the answer:

SQL> SELECT EVENT_ID FROM  2      ( SELECT SUM(CASE  3                  WHEN EVENT_LOCATION = 'LOC1' AND EVENT_TRIGGER = 'I' THEN 1  4                  WHEN EVENT_TRIGGER = 'D' THEN - 1  5               END) OVER( PARTITION BY EVENT_ID ORDER BY EVENT_CHANGE_DATE ) AS SM,  6               T.*  7          FROM EVENTS T  8      ) T  9  WHERE EVENT_LOCATION = 'LOC7' AND SM = 0;EVENT_ID------------EVENT1SQL>

Cheers!!