Question 112922 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – find id for which there is atleast one entry in table for every state

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.

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
  3. 0

    • 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search