This repository contains a collection of SQL queries for various energy management and analysis tasks.
Description: Retrieve energy consumption data for a customer with ID C001
, ordered by the reading date in descending order.
-- Retrieve energy usage for customer C001
SELECT customer_id, reading_date, energy_consumption
FROM energy_usage
WHERE customer_id = 'C001'
ORDER BY reading_date DESC;
Description: Calculate total energy consumption for each region, sorted by consumption in descending order.
-- Summarize energy consumption by region
SELECT region, SUM(energy_consumption) AS total_energy
FROM energy_usage
GROUP BY region
ORDER BY total_energy DESC;
Description: Find customers whose total energy consumption exceeds 1,000 units.
-- Identify customers with high energy consumption
SELECT customer_id, SUM(energy_consumption) AS total_consumption
FROM energy_usage
GROUP BY customer_id
HAVING SUM(energy_consumption) > 1000;
Description: Identify smart meters that have not sent readings in the past 24 hours.
-- Locate inactive smart meters
SELECT meter_id, last_reading_time
FROM smart_meters
WHERE last_reading_time < DATEADD(HOUR, -24, GETDATE());
Description: Retrieve the top 5 instances of peak energy usage, grouped by date and time.
-- Retrieve top 5 peak energy usage times
SELECT reading_date, reading_time, MAX(energy_consumption) AS peak_consumption
FROM energy_usage
GROUP BY reading_date, reading_time
ORDER BY peak_consumption DESC
LIMIT 5;
Description: Calculate the average duration of power outages for each region.
-- Compute average outage duration by region
SELECT region, AVG(DATEDIFF(MINUTE, outage_start_time, outage_end_time)) AS avg_outage_duration
FROM outages
GROUP BY region;
Description: Identify power grids that exceeded their capacity at any point.
-- Detect overloaded power grids
SELECT grid_id, MAX(load) AS peak_load, capacity
FROM power_grids
GROUP BY grid_id, capacity
HAVING MAX(load) > capacity;
Description: Retrieve billing details for all customers for January 2024.
-- Get billing details for January 2024
SELECT customer_id, billing_date, total_amount
FROM bills
WHERE billing_date BETWEEN '2024-01-01' AND '2024-01-31';
Description: Find power lines that experienced more than 5 failures.
-- Identify power lines with frequent failures
SELECT power_line_id, COUNT(*) AS failure_count
FROM failures
GROUP BY power_line_id
HAVING COUNT(*) > 5;
Description: Calculate the efficiency percentage of each power plant based on output and input.
-- Calculate power plant efficiency
SELECT plant_id, (SUM(output) / SUM(input)) * 100 AS efficiency_percentage
FROM power_plants
GROUP BY plant_id;
Description: Identify customers who have consistently paid their bills on time.
-- Find customers with no late payments
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN payments p ON c.customer_id = p.customer_id
WHERE p.payment_status = 'On Time';
Description: Summarize the total renewable energy produced by each plant.
-- Summarize renewable energy production by plant
SELECT plant_id, SUM(energy_produced) AS renewable_energy
FROM energy_production
WHERE energy_type = 'Renewable'
GROUP BY plant_id;
Description: Retrieve the top 5 customers who have contributed the most revenue.
-- Retrieve top 5 revenue-generating customers
SELECT customer_id, SUM(bill_amount) AS total_revenue
FROM bills
GROUP BY customer_id
ORDER BY total_revenue DESC
LIMIT 5;
Description: Identify power outages that impacted more than 1,000 customers.
-- Find outages affecting over 1,000 customers
SELECT outage_id, affected_customers, outage_start_time, outage_end_time
FROM outages
WHERE affected_customers > 1000;
Description: Calculate the average energy consumption for each hour of the day.
-- Analyze hourly energy consumption trends
SELECT HOUR(reading_time) AS hour_of_day, AVG(energy_consumption) AS avg_consumption
FROM energy_usage
GROUP BY HOUR(reading_time)
ORDER BY hour_of_day;
Description: Identify customers who have received more than one disconnection notice.
-- Find customers with multiple disconnection notices
SELECT customer_id, COUNT(*) AS notice_count
FROM disconnection_notices
GROUP BY customer_id
HAVING COUNT(*) > 1;
Description: Calculate the total revenue lost due to outages lasting more than one hour.
-- Calculate revenue loss due to long outages
SELECT SUM(estimated_loss) AS total_loss
FROM outages
WHERE outage_duration > 60;
Description: Summarize energy production by source and month.
-- Summarize energy production by type and month
SELECT energy_type, MONTH(production_date) AS month, SUM(energy_produced) AS total_energy
FROM energy_production
GROUP BY energy_type, MONTH(production_date)
ORDER BY month;
Description: Identify meter readings that are significantly higher than the average.
-- Detect suspiciously high meter readings
SELECT meter_id, reading_date, energy_consumption
FROM energy_usage
WHERE energy_consumption > (SELECT AVG(energy_consumption) * 3 FROM energy_usage);
Description: Compute the average billing amount for each customer.
-- Compute average billing amount by customer
SELECT customer_id, AVG(bill_amount) AS avg_bill
FROM bills
GROUP BY customer_id;