Top 10% Customers by Usage | Dataford Interview Questions

Your question is Top 10% Customers by Usage. 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.

Problem

Pacific Gas and Electric needs a regional view of its highest-consuming customers. Write a PostgreSQL query that identifies the top 10% of customers in the North Bay and Sierra regions using their total electricity consumption during the first quarter of 2025.

Requirements

Join customers with meter_readings and sum each customer's consumption_kwh from January 1 through March 31, 2025.
Rank customers independently within each region by total consumption in descending order.
Return the top 10% of customers per region. Round the threshold up so each region returns at least one customer.
Include the region, customer identifier, customer name, total consumption, regional rank, and regional customer count. Break consumption ties by the lowest customer_id.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique PG&E customer identifier
customer_name	VARCHAR(100)	Customer name
region	VARCHAR(50)	PG&E service region

meter_readings

Column	Type	Description
reading_idPK	INT	Unique meter reading identifier
customer_id	INT	References customers.customer_id
reading_date	DATE	Date of measured consumption
consumption_kwh	DECIMAL(10,2)	Electricity consumed in kilowatt-hours