сравнить две даты между разными записями

Почему бы не использовать могучий LEAD / LAG?

SELECT * FROM (
SELECT id,
       event_type,
       event_date,
       user_id,
       CASE
         WHEN event_type = 'USER_DELETED' THEN LEAD(EVENT_DATE) OVER (PARTITION BY useR_id ORDER BY event_type DESC) 
         ELSE NULL
       END  AS CREATED
FROM   user_events
WHERE event_type IN ('USER_ACTIVATED','USER_DELETED')
)
WHERE created > event_date

спасибо всем за помощь! вы, ребята, потрясающие !! я пошел со 2-м запросом из первого ответа на этот пост:

SELECT USER_ID
FROM   USER_EVENTS
GROUP BY USER_ID
HAVING MIN( CASE EVENT_TYPE WHEN 'USER_DELETED' THEN EVENT_DATE END )
         < MIN( CASE EVENT_TYPE WHEN 'USER_ACTIVATED' THEN EVENT_DATE END )

работает как шарм, спасибо @MTO

Я не могу решить, если это взломать, но просто для удовольствия:

select user_id
from   user_events
group by user_id
having listagg(event_type, ',') within group (order by event_date) like '%DELETED,%ACTIVATED%';

Для одноразового упражнения (при условии, что базовая таблица не слишком велика), простым решением является подзапрос. Выберите всех удаленных пользователей, затем найдите все соответствующие активированные записи с более поздней датой события:

with del as ( 
 select user_id
        , event_date as date_deleted
 from user_events
 where event_type = 'USER_DELETED'
)
select del.user_id
      , del.date_deleted
      , act.event_date as date_activated
from del
     join user_events act
          on act.user_id = del.user_id
where act.event_type = 'USER_ACTIVATED'
and del.date_deleted < act.event_date
order by del.user_id
/

Как показывают другие ответы, существует много способов написать выше. Другое простое решение - самосоединение:

select del.user_id
      , del.date_deleted
      , act.event_date as date_activated
from user_events del
     join user_events act
          on act.user_id = del.user_id
where del.event_type = 'USER_DELETED'
and act.event_type = 'USER_ACTIVATED'
and del.date_deleted < act.event_date
order by del.user_id
/

Просто найдите удаления перед активацией:

With activations as
( select * from t where EVENT_TYPE="USER_ACTIVATED"
),
deletions as
( select * from t where EVENT_TYPE="USER_DELETED"
),
select *
from deletions d
left outer join activations a
on d.USER_ID = a.USER_ID and
   d.EVENT_DATE < a.EVENT_DATE   --here

Вы можете использовать внутреннее соединение между набором строк строки, соединенным user_id и датой

select  user_id, event_date 
from USER_EVENTS  U
inner join  
(
select  user_id, event_date 
from USER_EVENTS 
where EVENT_TYPE ='USER_DELETED'
) T ON T.event_date < U.event_date 
    AND T.user_id= U.user_id  

WHERE EVENT_TYPE ='USER_ACTIVATED' 

вы можете попробовать, как показано ниже, используя объединение и подзапрос

select t1.USER_ID from 
(
select USER_ID,max(EVENT_DATE) as EVENT_DATE from 
USER_EVENTS where EVENT_TYPE='USER_ACTIVATED'
group by USER_ID
) t1 
join
select * from 
(
select USER_ID,max(EVENT_DATE) as EVENT_DATE from 
USER_EVENTS where EVENT_TYPE='USER_DELETED'
 group by USER_ID
) t2 on t1.=t2.USER_ID and t2.EVENT_DATE>t1.EVENT_DATE

Вы можете использовать аналитическую функцию COUNT, которая не потребует от вас самостоятельного объединения на вашем столе.

Oracle Setup :

CREATE TABLE USER_EVENTS ( ID, EVENT_TYPE, EVENT_DATE, USER_ID ) AS
SELECT 1, 'USER_DELETED',    DATE '2019-01-10', 5301 FROM DUAL UNION ALL
SELECT 2, 'USER_ACTIVATED',  DATE '2019-01-09', 5301 FROM DUAL UNION ALL
SELECT 3, 'USER_DELETED',    DATE '2019-01-05', 5302 FROM DUAL UNION ALL
SELECT 4, 'USER_ACTIVATED',  DATE '2019-01-11', 5302 FROM DUAL UNION ALL
SELECT 5, 'USER_DELETED',    DATE '2019-01-01', 5288 FROM DUAL UNION ALL
SELECT 6, 'USER_DELETED',    DATE '2019-01-02', 5287 FROM DUAL UNION ALL
SELECT 7, 'USER_CREATED',    DATE '2018-12-01', 5211 FROM DUAL UNION ALL
SELECT 8, 'USER_NOTE',       DATE '2018-12-01', 5211 FROM DUAL;

Запрос 1 :

SELECT *
FROM   (
  SELECT u.*,
         COUNT( CASE event_type WHEN 'USER_ACTIVATED' THEN 1 END )
           OVER (
             PARTITION BY user_id
             ORDER BY event_date
             ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
           ) AS num_activated
  FROM   USER_EVENTS u
)
WHERE  num_activated > 0
AND    event_type = 'USER_DELETED';

Результаты :

ID | EVENT_TYPE   | EVENT_DATE | USER_ID | NUM_ACTIVATED
-: | :----------- | :--------- | ------: | ------------:
 3 | USER_DELETED | 05-JAN-19  |    5302 |             1

Запрос 2 :

Если вы просто хотите, чтобы затронутые USER_ID с, вы можете использовать GROUP BY и HAVING:

SELECT USER_ID
FROM   USER_EVENTS
GROUP BY USER_ID
HAVING MIN( CASE EVENT_TYPE WHEN 'USER_DELETED' THEN EVENT_DATE END )
         < MIN( CASE EVENT_TYPE WHEN 'USER_ACTIVATED' THEN EVENT_DATE END )

| USER_ID |
| ------: |
|    5302 |

db <> fiddle здесь