Your question is Window Functions for Asset Ranking. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Write a SQL query using window functions to find the top-performing energy assets per region over the last quarter. Use the energy_assets and asset_performance tables, and treat performance as total production_mwh during the previous calendar quarter.
region, asset_id, asset_name, total_generation_mwh, and performance_rank.region ascending, performance_rank ascending, and asset_id ascending.| Column | Type | Description |
|---|---|---|
| asset_idPK | INT | Unique energy asset identifier |
| asset_name | VARCHAR(100) | Displayed name of the energy asset |
| region | VARCHAR(50) | Operating region for the asset |
| asset_type | VARCHAR(30) | Technology type of the asset |
| status | VARCHAR(20) | Current operating status |
| Column | Type | Description |
|---|---|---|
| performance_idPK | INT | Unique performance reading identifier |
| asset_id | INT | Referenced energy asset |
| reading_date | DATE | Date of the production reading |
| production_mwh | DECIMAL(12,2) | Energy production in megawatt-hours |