Hands-on Tutorials
In my last article, I spoke about my transition into Data Analytics and how I recently landed a full-time Data Analyst position. Throughout the month of April ’21, I was breezing in and out of interviews with various North American companies. For some of these companies, I had to partake in Excel, SQL, or Python tests while a few others had me work on case studies. In this article, I will walk you through one of such case studies which I passed and my approach in tackling the problem.

The Task
First, case studies are a way for companies to test core skills before considering you for advanced interview stages. For this case study, I was tasked with analysing the New York City Motor Vehicles Collision dataset in Google BigQuery from Jan 2014 to Dec 2017 and provide recommendations to reduce occurrence of accidents in Brooklyn, __ a borough in New York. The entire dataset currently has over 1.7 million records from 2012 to date and can be accessed here.
Side note: Google BigQuery has several public datasets that are updated periodically and can be used to build projects for your portfolio.
My Approach
My first instinct was to search the web for articles related to the task because "there’s nothing new under the sun". I found previous articles which I found useful in developing my approach. A summary of my approach is shown in the image below.

First Steps
Here are a few tricks and steps you should use to approach future case studies.
- Understanding the task: This is relevant for any case study to ensure that your analysis does not go off-point. It is important to follow the instructions first before going the extra mile. In this case study, I almost missed where I was asked to analyse only 2014–2017 data in the brief.
- Prepping the Data: Identifying the primary key and checking for duplicates and null values should be a no-brainer when exploring your dataset. Also look out for fields that might be relevant to your analysis, so you do not end up importing irrelevant fields into your Business Intelligence tool. This is where SQL came in handy.

Deep Dive
To analyse the dataset, I made use of Tableau Public for two reasons: I wanted to create an interactive dashboard and Tableau was one of the skill sets mentioned in the job description. From exploring the dataset, I got ideas of key features to do an in-depth analysis on. Some are highlighted below while others can be explored in the final dashboard.
- Collision Analysis: This was done to reveal top causes that led to collisions and fatalities. We can see here that most fatalities were caused by Driver Inattention/Distraction.

- Time Series Analysis: Reveal what time of day or day of week have most collisions. We can see from the chart below that most collisions occurred during rush hour (4PM–5PM). We also see significant numbers at early hours of the day.

- Fatality Analysis: This revealed that pedestrians were killed more often than other road users whenever collisions occurred.

Bringing it all together
Using the insights gathered from my analysis, I prepared a slide deck to provide recommendations. An additional tip is to ensure any recommendation you provide is backed up by your analysis — not prior knowledge. Also, most companies would give a few hours to 5 business days to complete a case study. If you see you have more time, please try not to rush through it.

The final submission for this case study was a slide deck and dashboard. The latter was an add-on because this was a major tech company and they loved it :). A preview of the interactive dashboard is shown below. I designed the background in Figma, and the rest of the magic happened in Tableau.
