Energy stability is one of the key themes the AEMR management team cares about. To ensure energy security and reliability, AEMR needs to understand the following:

1. What are the most common outage types and how long do they tend to last?
2. How frequently do the outages occur?
3. Are there any energy providers that have more outages than their peers which may indicate that these providers are unreliable?

These questions have been broken down into more detail below. Please download a copy of the dataset located here.

Note about using the dataset: The column names supplied in the sample tables must exactly match the column names in your SQL query. This is because the platform is CASE SENSITIVE. If they do not match, the Next.Tech Checker will not mark your query as correct even if the numbers are correct but the column names are different.

Please note that for the whole AEMR Case Study we are only interested in the Outages where the Status = Approved.

Upon completing all these questions, download the AEMR.csv file and use this in Tableau/PowerBI to craft your story. You can check your answers you’ve created using the .csv files you’ve downloaded from each of the Queries you’ve completed.

Part 1

SELECT COUNT(Status) AS Total_Number_Outage_Events,
Status,
Reason
FROM AEMR
WHERE Start_Time < '2017'
AND Status = 'Approved'
GROUP BY Reason
Order BY Reason

Part 2

SELECT COUNT(Status) AS Total_Number_Outage_Events,
Status,
Reason
FROM AEMR
WHERE Start_Time > '2017'
AND Status = 'Approved'
GROUP BY Reason
Order BY Reason

Part3

SELECT
Status,
Reason,
COUNT(Status) AS Total_Number_Outage_Events,
ROUND((AVG(TIMESTAMPDIFF(MINUTE, Start_Time, End_Time))/60)/24,2) Average_Outage_Duration_Time_Days,
YEAR(Start_Time) AS Year
FROM AEMR
WHERE Status = 'Approved'
GROUP BY Reason,Year
Order BY Reason,Year

Part4

Status,
Reason,
COUNT(Status) AS Total_Number_Outage_Events,
MONTH(Start_Time) AS Month
FROM AEMR
WHERE Status = 'Approved'
AND Start_Time < '2017'
GROUP BY Reason, Month
Order BY Reason, Month

Part5

SELECT
Status,
Reason,
COUNT(Status) AS Total_Number_Outage_Events,
MONTH(Start_Time) AS Month
FROM AEMR
WHERE Status = 'Approved'
AND Start_Time > '2017'
GROUP BY Reason, Month
Order BY Reason, Month

Part6

SELECT
Status,
COUNT(Status) AS Total_Number_Outage_Events,
MONTH(Start_Time) AS Month,
YEAR(Start_Time) AS Year
FROM AEMR
WHERE Status = 'Approved'
GROUP BY Month, Year
Order BY Month, Year

Part7

SELECT
COUNT(Status) AS Total_Number_Outage_Events,
Participant_Code,
Status,
YEAR(Start_Time) AS Year
FROM AEMR
WHERE Status = 'Approved'
GROUP BY Participant_Code, Year
Order BY Participant_Code, Year

Part8

SELECT
Participant_Code,
Status,
YEAR(Start_Time) AS Year,
ROUND((AVG(TIMESTAMPDIFF(MINUTE, Start_Time, End_Time))/60)/24,2) As Average_Outage_Duration_Time_Days
FROM AEMR
WHERE Status = 'Approved'
GROUP BY Participant_Code, Year
Order BY Average_Outage_Duration_Time_Days

Part9

SELECT
Count(Status) AS Total_Number_Outage_Events,
Reason,
YEAR(Start_Time) AS Year
FROM AEMR
WHERE Status = 'Approved'
AND Reason = 'Forced'
GROUP BY Reason, Year
Order BY Reason, Year

Part10

SELECT
SUM(CASE WHEN Reason = 'Forced' THEN 1 ELSE 0 END) AS Total_Number_Forced_Outage_Events,
Count(Status) AS Total_Number_Outage_Events,
ROUND(SUM(CASE WHEN Reason = 'Forced' THEN 1 ELSE 0 END) / Count(Status) * 100, 2) AS Forced_Outage_Percentage,
YEAR(Start_Time) AS Year
FROM AEMR
WHERE Status = 'Approved'
GROUP BY Year
Order BY Year

Part11

SELECT
Status,
YEAR(Start_Time) AS Year,
ROUND(AVG(Outage_MW), 2) AS Avg_Outage_MW_Loss,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, Start_Time, End_Time)),2) As Average_Outage_Duration_Time_Minutes
FROM AEMR
WHERE Reason = 'Forced' AND Status = 'Approved'
GROUP BY Year
Order BY Year

Part12

SELECT
Status,
Reason,
YEAR(Start_Time) AS Year,
ROUND(AVG(Outage_MW), 2) AS Avg_Outage_MW_Loss,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, Start_Time, End_Time)),2) As Average_Outage_Duration_Time_Minutes
FROM AEMR
WHERE Status = 'Approved'
GROUP BY Year, Reason
Order BY Year

Part13

SELECT
Participant_Code,
Status,
YEAR(Start_Time) AS Year,
ROUND(AVG(Outage_MW), 2) AS Avg_Outage_MW_Loss,
ROUND(((AVG(TIMESTAMPDIFF(MINUTE, Start_Time, End_Time)))/60)/24,2) As Average_Outage_Duration_Time_Days
FROM AEMR
WHERE Status = 'Approved' AND Reason = 'Forced'
GROUP BY Year, Participant_Code
Order BY Year, Avg_Outage_MW_Loss DESC

Part14

SELECT
Participant_Code,
Facility_Code,
Status,
YEAR(Start_Time) AS Year,
ROUND(AVG(Outage_MW), 2) As Avg_Outage_MW_Loss,
ROUND(SUM(Outage_MW), 2) AS Summed_Energy_Lost
FROM AEMR
WHERE Status = 'Approved' AND Reason = 'Forced'
GROUP BY Year, Participant_Code, Facility_Code
Order BY Year, Summed_Energy_Lost DESC