Window Functions for Top Customers | Dataford Interview Questions

Your question is Window Functions for Top Customers. 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 customer electricity consumption for 2025. Write a PostgreSQL query that identifies the top three customers by total usage within each PG&E service region.

Requirements

Sum each customer's usage_kwh during calendar year 2025.
Include customers with no qualifying usage, treating their total as zero.
Rank customers independently within each region using a window function.
Return the top three customers per region, ordered by region, usage descending, and customer ID as a deterministic tie-breaker.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique PG&E customer identifier
customer_name	VARCHAR(100)	Customer account name
region	VARCHAR(50)	PG&E service region
service_start_date	DATE	Date the customer's service began

usage_records

Column	Type	Description
usage_idPK	INTEGER	Unique usage record identifier
customer_id	INTEGER	Referenced customer identifier
usage_date	DATE	Date of the electricity usage measurement
usage_kwh	NUMERIC(12,2)	Measured electricity usage in kilowatt-hours