Discussion on: #SQL30 Day 1: Wildfires

View post

What a great idea, thanks for setting this up :D

This may be a little convoluted but ....

I would check out how many causes there were first:

select distinct cause
from day1.wildfire
order by cause

-- 13 rows

Then use a Window Function to order the fires by their cause and assign a rank in descending order.

Then use a CTE to isolate the Window Function results and return all rows where the rank = 1.

with rankings as
(select
  fire_name,
  cause,
  disc_date,
  cont_date,
  state,
  acres,
  dense_rank() over(partition by cause order by acres desc) as fire_rank
from 
  day1.wildfire
 order by cause, fire_rank
)

select *
from rankings
where rankings.fire_rank = 1

-- 20 seconds to execute

Then do some checks to make sure I've got the right results

select fire_name, max(acres) as acresmax
from day1.wildfire
where cause  = 'Campfire'
group by fire_name
order by acresmax desc

zchtodd • Oct 4 '19

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.