SQL find rows associated with current row based on column value

user_13592548 0
Viewed 45

Say I have a system where users get tokens based on activity. For any token X, I'd like to know what other tokens users with token X has earned.

For example:

Table Tokens:
PrimaryKey = TokenID

TokenId | UserID | TokenName | TokenGiver
-----------------------------------------
    111 |      1 |    token_a|        bob
    222 |      1 |    token_b|        bob
    333 |      1 |    token_c|        bob
    444 |      2 |    token_a|        bob
    555 |      2 |    token_c|        bob
    666 |      3 |    token_b|        bob
    777 |      4 |    token_c|        bob
    888 |      5 |    token_d|        bob

Expected output, something close to this:

TokenName | count | AlsoEarned
------------------------------
  token_a |     2 | [token_b: 1, token_c: 2]
  token_b |     2 | [token_a: 1, token_c: 1]
  token_c |     3 | [token_a: 2, token_b: 1] 
  token_d |     1 | []

Explanation: For token_a, UserID = 1 has earned it, as well as UserID = 2. UserID = 1 has also earned token_b and token_c and UserId = 2 has also earned token_c. Thus for token_a, we have [token_b: 1, token_c: 2]

google-bigquery sql
2 Answers

You can get the basic data with self-join

select t1.token_name, t2.token_name, count(*) as cnt
from tokens t1 join
     tokens t2
     on t1.user_id = t2.user_id and t1.token_name <> t2.token_name
group by t1.token_name, t2.token_name;

Then you seem to want an aggregation on top of this:

select token1, num_users, array_agg(tt) as also_earned
       from (select t1.token_name as token1, t2.token_name as token2, count(*) as cnt, t1.num_users
      from (select t1.*, count(distinct user_id) over (partition by token_name) as num_users
            from tokens t1
           ) t1 join
           tokens t2
           on t1.user_id = t2.user_id and t1.token_name <> t2.token_name
      group by t1.token_name, t2.token_name, t1.num_users
     ) tt
group by token1, num_users
user_11440350

Below is for BigQuery Standard SQL

#standardSQL
WITH temp AS (
  SELECT TokenName, UserID, COUNT(1) cnt 
  FROM `project.dataset.table`  
  GROUP BY TokenName, UserID
)
SELECT 
  TokenName, 
  ANY_VALUE(cnt) cnt,
  STRING_AGG(FORMAT('%s: %i', alsoTokenName, alsocnt), ', ') AlsoEarned
FROM (
  SELECT TokenName, SUM(cnt) AS cnt
  FROM temp
  GROUP BY TokenName
) 
LEFT JOIN (
  SELECT a.TokenName, b.TokenName AS alsoTokenName, SUM(b.cnt) AS alsocnt
  FROM temp a LEFT JOIN temp b USING (UserID)
  WHERE a.TokenName != b.TokenName
  GROUP BY TokenName, alsoTokenName
) 
USING(TokenName)
GROUP BY TokenName
-- ORDER BY TokenName

If to apply to sample data from your question - the output is

Row TokenName   cnt     AlsoEarned   
1   token_a     2       token_b: 1, token_c: 2   
2   token_b     2       token_a: 1, token_c: 1   
3   token_c     3       token_a: 2, token_b: 1   
4   token_d     1       null
user_52219440
Related