Walk me through a complex SQL query involving multiple JOIN operations (inner, outer, and self-joins).
