Calculate the number of users based on the below conditions by writing a single query SQL

There are two campaigns running campaign A and Campaign B and list of user ids participated in those two campaign is given below.

I am not able to figure out how to write in single SQL query.

Answer

Assuming you have two different tables, you can use union all and aggregation:

select in_a, in_b, count(*) as num_users
from ((select user_id, 1 as in_a, 0 as in_b
       from a
      ) union all
      (select user_id, 0 as in_a, 1 as in_b
       from b
      )
     ) u
group by in_a, in_b;

This gives you all the information you need. You can use group by in_a, in_b with rollup to get all combinations.

Or, you can summarize this into one row:

select sum(in_a) as in_a, sum(in_b) as in_b,
       sum(in_a * (1 - in_b)) as in_a_only,
       sum(in_b * (1 - in_a)) as in_b_only,
       sum(in_a * in_b) as in_ab
from ((select user_id, 1 as in_a, 0 as in_b
       from a
      ) union all
      (select user_id, 0 as in_a, 1 as in_b
       from b
      )
     ) u;

Note: These both assume that users are unique in each campaign. If not, just use select distinct or union in the subquery.