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

Mysql – SQL: select users that are in both test variant A en B

hope you can help write a sql query for table X.

I have a table with rows per timestamp, filled with User_ID’s and weather they have been in variant A or B of the a/b test.

I want to select all the User_ID’s that where In both A and B. In this table example that would only give me
User_ID 4.

Next I would like to drop the rows of these User_ID’s where A=1, so I only have B=1 left.

Who know how to write this?

timestamp User_ID In_variant_A In_variant_B
10-10-2023 1 0 1
11-10-2023 2 1 0
12-10-2023 3 1 0
13-10-2023 4 1 0
14-10-2023 4 0 1
15-10-2023 5 1 0
16-10-2023 6 1 0
17-10-2023 7 0 1
18-10-2023 7 0 1
19-10-2023 8 1 0

I tried using an where statement for where A=1 and B=1, nut that just gives me 0 records

Tags:

2

Answers


  1. 0

    To find User_IDs that have been in both variants A and B at different timestamps, we first need to identify those users. Then, from those users, we want to fetch only the rows where In_variant_B = 1.

    Given the provided table structure, here’s how you can write the SQL:

    WITH UsersInBothVariants AS (
    SELECT User_ID
    FROM yourTableName
    GROUP BY User_ID
    HAVING SUM(In_variant_A) > 0 AND SUM(In_variant_B) > 0
)

SELECT t.timestamp, t.User_ID, t.In_variant_A, t.In_variant_B 
FROM yourTableName AS t
JOIN UsersInBothVariants AS u ON t.User_ID = u.User_ID
WHERE t.In_variant_B = 1;

    Make sure to replace yourTableName with the actual name of your table.

    When you run this query, you’ll get the rows for users who were in both variant A and B, but only the records where In_variant_B = 1 will be shown.

    Login or Signup to reply.
  2. 0

    Another simple solution would be using a subquery with the matching id and delete only the records where In_variant_B <> 1 for the matching ids.

    To identify all the User_ID’s that where In both A and B

    select User_ID 
from yourtable y
group by User_ID 
having max(In_variant_A) = 1 and max(In_variant_B) = 1;

    Delete query

    delete y.* 
from yourtable y
inner join (select User_ID 
            from yourtable y
            group by User_ID 
            having max(In_variant_A) = 1 and max(In_variant_B) = 1 
            ) id_to_del on id_to_del.User_ID=y.User_ID
where In_variant_B <> 1;

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search