I have two tables first table looks like
| key | value |
|---|---|
| key1 | word1 |
| key1 | word2 |
| key1 | word3 |
| key2 | word4 |
| key2 | word5 |
| key3 | word6 |
the second table looks like
| otherkey | value |
|---|---|
| otherkey1 | word1 |
| otherkey2 | word1 |
| otherkey2 | word2 |
| otherkey2 | word3 |
| otherkey2 | word9 |
| otherkey3 | word4 |
| otherkey3 | word5 |
| otherkey4 | word4 |
| otherkey5 | word1 |
| otherkey6 | word6 |
And what i want is
| key | otherkey | combined | count |
|---|---|---|---|
| key1 | otherykey1 | word1 | 1 |
| key1 | otherykey2 | word1 word2 word3 | 3 |
| key2 | otherykey3 | word 4 word5 | 2 |
| key3 | otherykey6 | word6 | 1 |
What I've come up with work but is dreadfully slow and I'm wondering if there isn't a better way to do this, table1 is around 25k rows, table 2 contains 25m rows
1 Value in table 1 can be 1000 times in table 2
And i'm only interested in the keys from table 1 that have multiple matches in table2
SELECT
*
FROM (
SELECT
key
,otherkey
,COUNT(otherkey) count
,STRING_AGG(s.[value], ' ') combined
,ROW_NUMBER() OVER (PARTITION BY keu ORDER BY COUNT(otherkey) DESC) rn
FROM table1 n
LEFT JOIN table2 s
ON s.value = n.value
AND key IS NOT NULL
GROUP BY key
,otherkey
) X
WHERE rn < 10;