Your question is Top 5% Energy Spike Users. 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.
Bidgely's energy analytics platform needs to identify customers whose consumption varies sharply from their typical usage. Write a PostgreSQL query using common table expressions to find the top 5% of users by energy spike score.
Define a user's energy spike score as their maximum recorded usage_kwh minus their average non-null usage_kwh during the analysis period. Users without valid usage readings should not be ranked.
PERCENTILE_CONT.| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique customer identifier |
| user_name | VARCHAR(100) | Customer name |
| region | VARCHAR(50) | Customer service region |
| Column | Type | Description |
|---|---|---|
| usage_idPK | INTEGER | Unique energy usage reading identifier |
| user_id | INTEGER | References users.user_id |
| reading_date | DATE | Date of the energy reading |
| usage_kwh | NUMERIC(10,2) | Energy consumed in kilowatt-hours |