Second-Highest Usage per Customer | Dataford Interview Questions

Write a SQL query to find the second-highest energy consumption record for each utility customer from a transaction table at Smart Energy Water.

Treat second-highest as the second distinct consumption value for each customer. Include every transaction tied at that value.

Output

One row per qualifying transaction, with customer_id, transaction_id, and energy_consumption.
Include only customers with at least two distinct non-NULL consumption values.
Order by customer_id, then transaction_id.

energy_transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for the energy transaction
customer_id	VARCHAR(20)	Utility customer identifier
transaction_date	DATE	Date when the consumption was recorded
energy_consumption	NUMERIC(12,2)	Energy consumed during the transaction period

Your question is Second-Highest Usage per Customer. Start with the requirements and the one table 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.

Write a SQL query to find the second-highest energy consumption record for each utility customer from a transaction table at Smart Energy Water.

Treat second-highest as the second distinct consumption value for each customer. Include every transaction tied at that value.

One row per qualifying transaction, with customer_id, transaction_id, and energy_consumption.
Include only customers with at least two distinct non-NULL consumption values.
Order by customer_id, then transaction_id.

energy_transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for the energy transaction
customer_id	VARCHAR(20)	Utility customer identifier
transaction_date	DATE	Date when the consumption was recorded
energy_consumption	NUMERIC(12,2)	Energy consumed during the transaction period

Your question is Second-Highest Usage per Customer. Start with the requirements and the one table 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.