I finally finished my last endpoint with a single query! I was really close to giving up and using a naive solution but I was able to figure out the query just in time. I ended up changing up the structure of my query to aggregate within my LEFT JOIN LATERAL clauses. Normally using regular LEFT JOINs would give me duplicated data that seemed to multiply off each other. LATERAL allows the sub-SELECT to refer to columns of FROM items that appear before it in the FROM list. What an interesting query! I'm not sure if anyone else did it like I did, but I'm very proud of my solution.