Get count of users common between two brands

user_10535439 0
Viewed 73

I have a table having columns brand and uid. It captures information when a particular user makes a transaction with a brand. The sample of table is as below

+-------+-----+
| brand | uid |
+-------+-----+
| A     | 111 |
| B     | 111 |
| C     | 111 |
| A     | 112 |
| B     | 112 |
| D     | 112 |
| A     | 114 |
| B     | 114 |
| C     | 114 |
| B     | 115 |
| C     | 115 |
| A     | 116 |
| D     | 116 |
+-------+-----+

I want the count of users common between two brands. For example if a user transacts with brand A then how many of them transact with brand B and so on (all possible combinations)

The desired output is as below

+--------+--------+------------+
| brand1 | brand2 | count_user |
+--------+--------+------------+
| A      | A      |          4 |
| A      | B      |          3 |
| A      | C      |          2 |
| A      | D      |          2 |
| B      | A      |          3 |
| B      | B      |          4 |
| B      | C      |          3 |
| B      | D      |          1 |
| C      | A      |          2 |
| C      | B      |          3 |
| C      | C      |          3 |
| C      | D      |          0 |
| D      | A      |          2 |
| D      | B      |          1 |
| D      | C      |          0 |
| D      | D      |          2 |
+--------+--------+------------+

The output should be read as below

  1. There are 4 users who have transacted with brand A (row 1)
  2. There are 3 users who have transacted with brand A and B (row 2)
  3. There are 2 users who have transacted with brand A and c (row 3)

and so on ........

hiveql hive count join sql
1 Answers

You can cross join distinct brands to generate all possible combinations, and then add two more joins to bring the corresponding users - finally, you can aggregate and count:

select b1.brand brand1, b2.brand brand2, count(t2.uid) count_users
from (select distinct brand from mytable) b1
cross join (select distinct brand from mytable) b2 
inner join mytable t1 
    on  t1.brand = b1.brand 
left  join mytable t2 
    on  t2.brand = b2.brand
    and t2.uid = t1.uid 
group by b1.brand, b2.brand
order by b1.brand, b2.brand

Demo on DB Fiddle:

brand1 | brand2 | (No column name)
:----- | :----- | ---------------:
A      | A      |                4
A      | B      |                3
A      | C      |                2
A      | D      |                2
B      | A      |                3
B      | B      |                4
B      | C      |                3
B      | D      |                1
C      | A      |                2
C      | B      |                3
C      | C      |                3
C      | D      |                0
D      | A      |                2
D      | B      |                1
D      | C      |                0
D      | D      |                2

This is no a Hive fiddle (there is none available), but the syntax is standard and would work just as well in Hive.

user_106767160
Related