Solving Linkedin Case Study — New York Motor Vehicle Collisions
Problem statement
A critical skill set we would like to test is your ability to analyze large data sets and present a set of actionable recommendations. For this exercise, we would like you to:
- Analyze data on New York motor vehicle collisions (details below) from Jan 2014 to date.
- Answer the following question: What are your recommendations for
reducing accidents in Brooklyn? - Imagine you are preparing this PowerPoint presentation for the city council who will use it to inform new legislation and/or projects. Specifically, what concrete actions will you recommend that the Brooklyn city council should take to reduce deaths and accidents?
- Presentation should be 3–5 slides max (appendix slides do not count against
the 5-slide max limit).
Instructions for accessing the dataset
Instructions to access this data through Google BigQuery are below.
Solution
Key Highlights
Traffic crashes can claim the lives of anyone in New York City. Every life is precious, so it is the responsibility of each residents, workers, and visitors to save life by their conscious effort.
Based on the New York Motor Vehicle Collision data few analysis were performed, and we noted that:
a. 428 people were killed and 1,04,336 people were injured in road accident which occurred between 2014 to 2022 in Brooklyn.
b. Majority of the collision reported in the Zip code: 11236 & 11207.
c. 57% of total deaths were of pedestrians.
d. Linden Boulevard & Atlantic Avenue were two such places with maximum casualties reported.
e. Top contributing factors for road accidents were Driver Inattention/Distraction& Failure to Yield Right-of-Way.
f. Station Wagon/Sport Utility Vehicle & Sedan are two majorly reported vehicles for accidental case.
Analysis & Interpretation
Time Wise Analysis
- Most accidents occurred between 02:00 PM to 07:00 PM & considered as rush hour. 05:00 PM to 06:00 PM tolls highest casualties with 7650 injuries and 27 deaths reported so far.
- Action Plan : Target Street Team outreach at Brooklyn Priority Corridors, Intersections, and Areas
Factor Wise Analysis
- Top contributing factors for road accidents were Driver Inattention/Distraction, Failure to Yield Right-of-Way, Unsafe Speed, Traffic control disregarded and Following too closely. Driver Inattention/Distraction leads to 25003 injuries and 63 deceased reported.
- Action Plan : Launch multilingual public information campaigns in Brooklyn Priority Areas.
Street Wise Analysis
- Linden Boulevard, Atlantic Avenue, Nostrand Avenue, Church Avenue and Flatlands Avenue were some places with maximum casualties reported in terms of injuries and person killed. Linden Boulevard alone reported 587injuries and 5 person were deceased in Flatlands Avenue.
- Action Plan : Expand pedestrian crossing time on all Brooklyn Priority Corridors.
- Sedan, Passenger Vehicle and SUVs substitute majorly in all the accidents reported in Brooklyn. With 56244 injuries and 7378 life lost Sedan constitute a major portion in the road accident.
- Action Plan : Install speed bumps and additional lighting under key transit stops in Brooklyn.
Recommendations
Based on the analysis carried out, below are a few recommendations for the Brooklyn City Council to consider implementing which can help to lower the accident rate:
- Reduce the citywide speed limit to 25 to 30 MPH.
- Regular Training programs can be introduced for drivers and residents in PPP mode & can be offered in public places & offices.
- Deploy more Traffic Police in the areas with zip code: 11236 & 11207.
- Construct overhead bridge or subways for pedestrians to cross busy roads.
- Places like Linden Boulevard & Atlantic Avenue can equipped with more sign boards, traffic controller, security camera and signals to reduce casualties and provide immediate medical help to the victims.
- Stringent fines and penalties for repeated offenders can be introduced.
- Residents can be encouraged to use public transport, go for car-pooling services and expand bicycle network.
APPENDIX
SQL Queries on GCP Big Query
