SQL Challenges · WEEKLY QUACK CHALLENGE!
THE FROG INVASION
Difficulty: hard
Lieutenant Teal bursts into the command room dripping wet. Something is happening out there and I dont like it one bit. The frogs — theyre multiplying. Every week the survey teams go out and count whats in each pond and I have been watching the numbers and Duckbert I am scared. One by one these ponds are going green. The frogs are taking over. I need to know which pond fell first. Not the pond with the most frogs — any fool can count a heap of frogs. I need the pond where the balance tipped ear…
Your task
Each surveyed species is a duck or a frog, classified by `read_csv(data/files/202631_05_frog_invasion/species_info.csv, header=true)`. For each pond, follow the running totals of ducks and frogs over time and find the first date its cumulative frog count overtakes its cumulative duck count. Return the name of the pond that crosses over on the earliest date; on a tie, the alphabetically first. You will be working with the `surveys` and `ponds` tables and the species file.