Top 5% Energy Spike Users | Dataford Interview Questions

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.

Requirements

Aggregate each user's maximum usage, average usage, and spike score.
Use a CTE to calculate the 95th percentile spike-score threshold with PERCENTILE_CONT.
Return users whose score is greater than or equal to that threshold, ordered from highest to lowest score.
Include the user's region and the number of valid readings.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique customer identifier
user_name	VARCHAR(100)	Customer name
region	VARCHAR(50)	Customer service region

energy_usage

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

Your question is Top 5% Energy Spike Users. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem