count changes based on timestamp

Question

I have a table

timestamp   ip        score
1432632348  1.2.3.4   9
1432632434  5.6.7.8   8
1432632447  1.2.3.4   9
1432632456  1.2.3.4   8
1432632460  5.6.7.8   8
1432632464  1.2.3.4   9

The timestamps are consecutive, but don't have any frequency. I want to count, per IP, the number of times the score changed. so in the example the result would be:

ip      count
1.2.3.4 3
5.6.7.8 1

How can I do that? (note: count distinct does not work: 1.2.3.4 changed 3 times but had 2 distinct scores)

Answer 1

select ip,
       sum(case when score <> (select t2.score from table t2
                               where t2.timestamp = (select max(timestamp) from table
                                                     where ip = t2.ip
                                                       and timestamp < t1.timestamp)
                                 and t1.ip = t2.ip) then 1 else 0 end)
from table t1
group by ip