Banu Alexandru Banu Alexandru - 3 months ago 6
SQL Question

Exclude rows based on values on other rows

I have the following values in a table called CART:

ID EMP_EMAIL PRODUCT ACTIVITY_TYPE
1 USER@EMAIL.COM Book Add
2 USER@EMAIL.COM Book Remove
3 USER@EMAIL.COM Kindle Add
4 USER@EMAIL.COM Kindle Remove
5 USER@EMAIL.COM Phone Add
6 USER@EMAIL.COM Phone Remove
7 USER@EMAIL.COM Book Add


At the end I need to remain with just one line, the last one Add Book.

The rows are ordered in sequential order. This means that the user selected a book. Then removed the book and selected a Kindle. Then removed the Kindle and selected a phone and so on.

At the end I just need to output that the user selected a Book.

This example could also have a null output:

ID EMP_EMAIL PRODUCT ACTIVITY_TYPE
1 USER@EMAIL.COM Book Add
2 USER@EMAIL.COM Book Remove
3 USER@EMAIL.COM Kindle Add
4 USER@EMAIL.COM Kindle Remove
5 USER@EMAIL.COM Phone Add
6 USER@EMAIL.COM Phone Remove


This shouldn't return anything as the last action was a remove.

I'm open to any suggestions either in sql or pl/sql.

Answer

If you want to select everything that was added, but not removed. Assuming no duplicates:

select c.*
from cart c
where c.activity_type = 'Add' and
      not exists (select 1
                  from cart c2
                  where c2.emp_email = c.emp_email and
                        c2.product = c.product and
                        c2.activity_type = 'Remove'
                 );

If you want to select items that have more adds then removes:

select c.emp_email, c.product
from cart
group by c.emp_email, c.product
having sum(case when c.activity_type = 'Add' then 1
                when c.activity_type = 'Remove' then -1
           end) > 0;