That's an awesome solution, thanks for writing that. I don't think it's convoluted at all.
In my solution I went without window functions and did a join to a subquery. The interesting thing is that I could potentially join to the wrong row if there were more than 1 fire with the same cause and max acres burned, whereas here I think any ties would end up with the same rank, but it's guaranteed that there's no mixing and matching of data.
I hope you like the rest of the series! I started it because I was one of those developers that thought they knew SQL, when really I knew SQL-92, and modern SQL has so much more to offer.
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.