Search code examples
sqlimpala

SQL - How to "filter out" people who has more than 1 status

I tried to find this question here but I probably didn't know the exact term to search for.

Here is the problem:

I have this set of customers (see image). I need to filter only those with status "user_paused" or "interval_paused". A same customer_id may have more than 1 status, and sometimes, this status can be "active". If so, this customer should not appear in my final result.

See customer 809 - he shouldn't appear in my final result since he has an "active" status. all the others are fine, because they only have paused statuses.

I still couldn't figure out how to proceed from here.

Thank you so much.

IMAGE HERE!

Solution

  • One method uses group by and having:

    select customer_id
from t
group by customer_id
having sum(case when status not in ('user_paused', 'interval_paused') then 1 else 0 end) = 0;