SQL Challenges · WEEKLY QUACK CHALLENGE!
THE POND-WIDE BLACKOUT
Difficulty: hard
Grid Chief Merganser clutches the grid map, feathers on end. The whole network went dark last night, Duckbert. Multiple ponds lost power — but one sat stone cold for longer than any other, unbroken, hour after hour. Some ponds flickered in and out more times than I can count. Dont be fooled by the total dark count. I need the pond that suffered the single longest continuous blackout. Find it.
Your task
A pond’s total load each hour is the combined megawatts of all its meters, and the pond is blacked out in any hour its total load is 1.0 MW or less. Readings are hourly with no gaps. Return the name of the pond with the longest unbroken run of consecutive blackout hours; on a tie, the alphabetically first. You will be working with the `power_readings`, `meters`, and `ponds` tables.
Tables
power_readings (288 rows)
| column | type |
|---|---|
meter_id | INTEGER |
ts | TIMESTAMP |
megawatts | DECIMAL(8,2) |
meters (12 rows)
| column | type |
|---|---|
meter_id | INTEGER |
pond_id | INTEGER |
ponds (4 rows)
| column | type |
|---|---|
pond_id | INTEGER |
name | VARCHAR |
Database: https://dbquacks.com/data/databases/weekly_202633_07_blackout.duckdb
This page as markdown: https://dbquacks.com/challenges/weekly/7.md