I have a bigquery table containing data from our search analytics.
The problem is our data contains partial queries as our search happens as the user types (with some debouncing), meaning we're left with lots of partial queries
Given a table like the below
| query |
| ---------------------------------------- |
| addic |
| addiction |
| adding |
| adding 4 digit numbers |
| adding and |
| adding and su |
| adding and sub |
| adding and subtracting fr |
| adding and subtracting negative numbers |
| adding subtracting decimals |
| additio |
| addition and |
| addition and sub |
| addition and subrtacion |
| addition and subtacion |
| addition and subtraction |
| addition numbers to |
| addition primary |
| addition subtraction |
| addition subtraction multiplic |
How can I find the "final" search terms (where each column is not a substring of another column)
| query |
| ---------------------------------------- |
| addiction |
| adding 4 digit numbers |
| addition primary |
| adding and subtracting fr |
| addition subtraction multiplic |
| adding and subtracting negative numbers |
| adding subtracting decimals |
| addition and subrtacion | <- typos, but technically different
| addition and subtacion |
| addition and subtraction |
I've tried inner joins / where in clauses but I haven't got anything close.
I'm open to completely different approaches also as the naive substring matching will rule out valid queries like "addition"