Challenge 4: Telemetry to the rescue!

Previous Challenge

Introduction

Now that we have detected the timestamp of the crash, let’s see if we can find out which drivers were involved. Using only visual data can be difficult so we’ll use the available telemetry data to determine the drivers. And this time we’ll use Python Notebooks from BigQuery Studio to run the analysis.

Description

The telemetry data is available here: gs://ghacks-genai-fe/telemetry/. Go ahead and load that into BigQuery.

We’ve already designed a Python notebook for this challenge. Upload that to BigQuery, and complete the two cells that have a TODO comment:

  1. Provide the SQL that returns the average speed and brake information per driver for the timeframe between 1 second before the crash and the crash timestamp
  2. Use the result of that query as part of the prompt to Gemini to get back the drivers involved in the crash.

Success Criteria

  • The telemetry data is available as a table in BigQuery.
  • There’s a SQL query that returns the average brake and speed from telemetry for each driver, aggregated over a second just before the crash.
  • There’s a prompt that uses the output of the SQL query as input to Gemini to determine the drivers that were involved in the crash.
  • Gemini outputs the correct drivers and a brief explanation of why.

Learning Resources

Tips

  • Note that the timestamp from the CCTV footage is in local time (CEST, which is UTC+2) whereas the telemetry data is in UTC.

Previous Challenge