find id for which there is atleast one entry in table for every state-CodePudding

I have a table named Users, with userId as one of the column, the user can be in any one of given 5 possible states which are (Unregistered, registered, subscribed, premier, unsubscribed).

I want a query which can give me a list of all those userIds which have gone through all the states.

Can someone help me out with that.

i am sharing a sample schema as well to understand the problem better

userId  state       created_at
1      Unregistered 1/10/22
2      Unregistered 4/10/22
3      registered   4/10/22
2      registered   5/10/22
1      registered   7/10/22
1      subscribed   12/10/22
2      subscribed   13/10/22
2      premier      22/10/22
2      unsubscribed 23/10/22
3      unsubscribed 25/10/22
1      unsubscribed 25/10/22

here, as you can see, only userId = 2 can be the correct answer, as it is going through all the required states.

CodePudding user response：

A simple having count would do the trick.

If the state are not known you could use:

select userId 
from Users
group by userId
HAVING COUNT( distinct state ) = (select count(distinct state) from Users);

https://dbfiddle.uk/UEnGQ53k

Otherwise if the states are known:

select userId 
from Users
 where state in ('Unregistered','registered','subscribed','premier','unsubscribed') 
group by userId
HAVING COUNT( distinct state ) = 5;

https://dbfiddle.uk/dBe69rP5

CodePudding user response：

You could count the number of unique statuses per userid and use that as your filter:

SELECT USERID
FROM (
  SELECT USERID, COUNT(DISTINCT STATUS) AS N_UNIQUE_STATUS
  FROM USERS
  GROUP BY USERID
) A
WHERE N_UNIQUE_STATUS = 5

dbfiddle here

CodePudding user response：

since you didn't mention if there can be other possible states or duplications, you can go with count logic

instead of groupby, i used the windows function so that you will be able to see all the rows and the dates of each id which has all 5 states

with main as (
  select *, 
    count(state)
    over(partition by userid) as total_states_per_userid  from users
  )
  select * from main where total_states_per_userid = 5