I am interested to follow this thread also. There is value in reading the code of others and in trying new things for yourself.
Much of my day is spent working with Oracle so the following may not apply directly to the SQL flavors you mention. I've learned how ranking and unpivot can be very helpful. On occasion I search
this on-line resource.