CODEWITHSUNDEEP
sqlgoogle-bigquerybq
clucko87
clucko87

Reputation: 89

How to check changes in column values?

I need to try to check some device IDs for work. These are values (15 characters, random string of numbers+letters) that mostly remain constant for users. However, every now and then these deviceIDs will change. And I'm trying to detect when they do change. Is there a way to write this kind of a dynamic query with SQL? Say, perhaps with a CASE statement?

user device date
1 23127dssds1272d 10-11
1 23127dssds1272d 10-11
1 23127dssds1272d 10-12
1 23127dssds1272d 10-12
1 04623jqdnq3000x 10-12

Upvotes: 0

Views: 1231

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Consider below approach

select *
from your_table
where true 
qualify device != lag(device, 1, '') over(partition by user order by date)

if applied to sample data in your question - output is

enter image description here

As you can see here - at 10-11 first 'change, assignment' happened for user=1 ; and then on 10-12 he device changed

Upvotes: 0

NickW
NickW

Reputation: 9768

Count distinct device by id having count > 1?

Upvotes: 1

Related Questions