Now aside from the odd occasion when you actually need it for it’s simple purpose it’s a rather interesting way to get a rolling count. Basically you can use it to get a list back of 1,2,3,…n-1,0 where n is your divisor.
There are a few great use cases for modulo within SQL Server. One not mentioned is building test data. You can easily build a uniformly distributed set of randomized numeric values within a particular range using modulo math.