Knowledge/technical Test
You will be assessed on;
1. Your ability work in SQL to develop a clean schema for analysis
2. The insights you arrive at
3. Your ability to communicate those insights effectively
*The intent of this exercise is not to be extensive or comprehensive. Concise relevant insights are the
valued above comprehensive observations.
Deliverables
You must Provide;
1. An entity-relationship diagram from a SQL environment that demonstrates how you have
modelled and cleaned the data for analysis. This should employ a star schema.
2. A report in any format that contains any dashboards, visualizations explanation of method and
insights you have gained from the data.
o The report is not to exceed 10 pages
o The report must be readable by the assessor
The Scenario
You are working to support a conference of State Level public policy advisors assessing the effectiveness
of State responses to COVID.
There is a need to demonstrate the efficacy of different public health responses at the State level. You
are being asked to provide a descriptive presentation that demonstrates where vaccine implementation
was the most effective.
Your analysis should be easy to understand but also be demonstrably routed in statistical and research
methodology.
The Approach
Using a local version mySQL workbench or any database tool you have available, create a database for
these two CSV files. Create a series of ingestion tables and manipulate them into a star schema with
both dimension tables and fact tables that will support your analysis.
United_States_COVID-
19_Cases_and_Deaths_by_State_over_Time (1).csv
COVID-19_Vaccinatio
ns_in_the_United_States_Jurisdiction.csv
Further Details about the attached data sets can be found here
https://data.cdc.gov/Case-Surveillance/United-States-COVID-19-Cases-and-Deaths-by-State-o/9mfq-
cb36/data
https://data.cdc.gov/Vaccinations/COVID-19-Vaccinations-in-the-United-States-Jurisdi/unsk-b7fc/data
https://data.cdc.gov/Case-Surveillance/United-States-COVID-19-Cases-and-Deaths-by-State-o/9mfq-cb36/data
https://data.cdc.gov/Case-Surveillance/United-States-COVID-19-Cases-and-Deaths-by-State-o/9mfq-cb36/data
https://data.cdc.gov/Vaccinations/COVID-19-Vaccinations-in-the-United-States-Jurisdi/unsk-b7fc/data
If not already installed, you can download a free version of mySQL here MySQL and create a local
instance on your machine (no server required).
You may do your subsequent analysis in SQL, excel, python, R or any other platform you can access. You
may generate a final report in any format you wish, so long as the assessor can access it.
There is no need to bring in any additional data.
Timelines
You have 5 calendar days from the time you receive this assignment – or 120 hours to email your
deliverables back to your sender.
https://dev.mysql.com/downloads/workbench/