Of course, nobody sits down and writes something like this from scratch. A bit of code like this evolves, over time, incrementally, as developers add to it.
And, as many of the commenters note, this statement actually packs an enormous amount of functionality into a very compact form. It is dense.
There's something like 20 tables mentioned in the FROM clause; I bet this monster is fun to run through a modern query optimizer!
In a previous job, I built and maintained a Continuous Integration system which operated a fleet of automated build robots that performed build and test tasks and provided tools to analyze and interpret the results. The core logic of the system was as follows:
- Design the underlying database schema as carefully as you can
- Express the primary operations of the system as database queries; pack as much intelligence as possible into the query itself
- Provide a thin layer of execution (in the build bots) and visualization (in the web UI) logic around the underlying database system; let the basic database structure show through (see point #1)
Many of our queries were rather complex; the most complex query was the one which scheduled the waiting jobs to the available bots, matching priorities and capabilities according to the system's rules. The query was hell to write, but once we got it built, the rest of the system just flowed.