This is a report using SQL join queries to retrieve information from a data warehouse with a star schema.
The report is based on a company who want to use their data warehouse, which stores incidents of server downtime in a fact table, to discern if the number of incidents has improved since the company started in January, in the first quarter of the year, and to understand where to focus their efforts in order to improve downtime issues.
The company owners have tasked the data analyst with answering a list of questions to determine if there are downtime issues with a specific time of the day, day of the week, day of the month, office or a specific employee.
The tables of the data warehouse required to investigate downtime incidents are shown in this diagram:
- The queries to answer the questions below are in the 'analysis_queries.sql' file.
- The 'create_tables.sql' file will create the data warehouse tables.
- All other 'insert...' files will insert data into the dw tables.
- The company owners would like the Data Analyst to report back and answer the questions below, particularly focusing on how the servers have performed in quarter 1 since January and any recommendations as to what to focus on to solve downtime problems.
OVERVIEW - QUARTER 1
-
What were the total number of incidents?
-
What was the max, min and avg downtime in minutes?
SPECIFIC ANALYSIS - QUARTER 1
-
Which day of the week had the most incidents?
-
Which day of the month had the most incidents?
-
Which time of day had the most incidents?
-
Which hour of the day had the most incidents?
-
Which month had the most incidents?
-
Which employee reported the most incidents, and which office are they based in?
-
Which office reported the most incidents?
-
Which office had the highest average downtime and what were the overall number of incidents per office?
See the SQL queries that answered the questions above:
https://github.com/richardgourley/data-analysis-report-with-sql/blob/main/analysis_queries.sql
See the final report based on the answers here:
https://github.com/richardgourley/data-analysis-report-with-sql/blob/main/final_report.MD