compare two dates between different records

Question

I have an issue with an SQL Query I'm trying to build on Oracle DB. I need to find out cases where some records have been created before other records. it sounds simple, but i'm having issues with it, i cant tell why.

there's a table that contains user detailed events, called "USER_EVENTS". we found a certain bug that involves deletion of users, before they were activated. I want to get all the Users that had this bug, so if i look at the table i'll see something like this:

TABLE USER_EVENTS

ID  EVENT_TYPE      EVENT_DATE  USER_ID
1   USER_DELETED    10/1/2019   5301
2   USER_ACTIVATED  9/1/2019    5301
3   USER_DELETED    5/1/2019    5302
4   USER_ACTIVATED  11/1/2019   5302
5   USER_DELETED    1/1/2019    5288
6   USER_DELETED    2/1/2019    5287
7   USER_CREATED    1/12/2018   5211
8   USER_NOTE       1/12/2018   5211

tried various queries i cant seem to match those two, i know its silly, i apologize.

looking at the table above, i want to get the USERIDs of those parcels that had the DELETED event before the ACTIVATED event. from the image, what i should get in return is "5302", which was deleted on the 5/1/2019 but activated on the 11/1/2019.

Thanks in advance!

P.S. - please dont get into a design issue with naming conventions or how to not have this bug etc', the above is just a general example.

user_1509264 · Answer

You can use the COUNT analytic function which will not require you to perform a self-join on your table.

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;

Query 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';

Results:

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

Query 2:

If you just want the affected USER_IDs then you can use GROUP BY and 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 )

Results:

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

db<>fiddle here

user_146325 · Answer

For a one-off exercise (and assuming the base table is not too huge) a simple solution is a sub-query. Select all the deleted users then find any matching activated records with a later event date:

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
/

As the other answers show, there are many ways to write the above. Another simple solution is the self-join:

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
/

user_6808583 · Answer

you can try like below by using join and subquery

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

user_842935 · Answer

Just look for deletions before activations:

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_3522312 · Answer

You could use an inner join between the tow set of row joined by user_id and date

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'

user_230471 · Answer

I can’t decide if this is a hack, but just for fun:

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

user_8432296 · Answer

Why not use the mighty 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

user_967591 · Answer

thanks everyone for helping out! you guys are amazing!! i went with the 2nd Query from the first answer to this post:

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 )

works like a charm, thanks @MTO

compare two dates between different records

8 Answers