Forgive me I’m not massively familiar with SQL to do the below. But would love to learn the process of how to do it if possible.
I only have one table: Table name – SessionTracker
----------------------------------------------------------------------------------------------------------------------------------- bundleID | sessionId | deviceID | eventType | codeValue ------------------------------------------------------------------------------------------------------------------------------------ com.package.random 3871207406642403679 333333-00000-0000-00000-000000000000000 REQUEST 1 com.package.random 3871207406642403679 333333-00000-0000-00000-000000000000000 EVENT 1 com.package.random 3871207406642403679 333333-00000-0000-00000-000000000000000 RESPONSE 1 com.package.random 3245233406642403679 000000-00000-0000-00000-000000000000000 REQUEST 1 com.package.random 3245233406642403679 000000-00000-0000-00000-000000000000000 EVENT 2 com.package.random 3245233406642403679 000000-00000-0000-00000-000000000000000 RESPONSE 2 com.package.random 871207406643e243433 000000-00000-0000-00000-000000000000000 REQUEST 1 com.package.random2 3243254325454535422 111111-00000-0000-00000-000000000000000 REQUEST 1 com.package.random3 4353453452525252465 222222-00000-0000-00000-000000000000000 REQUEST 1 com.package.random4 3453656456353252345 111111-00000-0000-00000-000000000000000 REQUEST 1 com.package.random5 4567568765745634563 111111-00000-0000-00000-000000000000000 REQUEST 1
I’d like to
Select all the sessions where the codeValue was different within that session.
From the example above:
I want to check is if a session which consists of request, event & response has a different value in one of each. Like the sessionId above (3871207406642403679) the code value is 1 in each so this wouldn’t be flagged.
The second sessionId (3245233406642403679) the code value in one of the request, event & response has the code value 2 for event and response, so this would be flagged.
I’m hoping a query in databricks would work, is this possible?
Advertisement
Answer
In SQL, you could do this with aggregation and a having clause:
select sessionId from mytable group by sessionId having min(codeValue) <> max(codeValue)
This gives you all sessionIds that have at least two distinct codeValues.