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 |
CodePudding user response：
Count distinct device by id having count > 1?
CodePudding user response：
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
As you can see here - at 10-11 first 'change, assignment' happened for user=1 ; and then on 10-12 he device changed