Identify Customers With Frequent Outages | Dataford Interview Questions

Given two tables of customer billing and grid outages, identify the customers affected by more than three outages in a single month using SQL.

Use the supplied billing month and outage timestamp data to determine which outages affect each customer. Return only customer-month combinations exceeding three outages.

Output

One row per qualifying customer and month.
Columns: customer_id, customer_name, outage_month, and outage_count.
Sort by customer_id, then outage_month ascending.

Schema

customer_billing

Column	Type	Description
billing_idPK	INT	Unique billing record identifier
customer_id	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer name
service_area	VARCHAR(50)	Grid service area associated with the customer
billing_month	DATE	First day of the customer's billing month
bill_amount	DECIMAL(10,2)	Monthly billed amount

grid_outages

Column	Type	Description
outage_idPK	INT	Unique grid outage identifier
service_area	VARCHAR(50)	Grid service area where the outage occurred
outage_start	TIMESTAMP	Timestamp when the outage started
outage_end	TIMESTAMP	Timestamp when the outage ended
affected_customers	INT	Number of customers affected by the outage

Your question is Identify Customers With Frequent Outages. 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