I think the important thing is to "establish my interface" first. Exactly what am I required to provide? What must the data look like? Do I need one result set or two? A scalar or a result set? How many rows per record set? What does the aggregation or nullability require of me? Etc.

Once I have that I try to determine the logical order to get that data. "Store this data in a temp table then ...". What are the major building blocks as I assemble the data and what order must it happen in?

From there I start at the "inside" of the first query and, iteratively, work outward. Then the next query, etc.

At least that's what I remember through the creative fog. 🙂