In BigQuery, match two tables according to a calculated value of one table

I have two tables in BigQuery: one containing the places of road cameras, named cameras…

| city | state |  road  | mile |
|------|-------|--------|------|
| Abcd | Wxyz  | 10101  | 12.3 |
| Efgh | Wxyz  | 98765  | 7.8  |
...

…and another containing accidents, named accidents.

| date  | street | mile |
|-------|--------|------|
| 12-01 | 10101  | 11.9 |
| 12-02 | 10101  | 21.1 |
| 12-02 | 10101  | 12.1 |
| 12-02 | 98765  | 7.1  |
| 12-03 | 98765  | 7.6  |
| 12-03 | 98765  | 5.3  |
...

I need to inspect how many accidents happened in the range of 0.5 mile before and 0.5 mile after the cameras.

So, what I need to do is:

1. Establish a .5-mile perimeter in table named cameras;
2. Check how many accidents in table accidents happened in each .5-mile perimeter of table cameras.

How can I do that? Any help?

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT c.*, accidents_in_perimeter FROM (
  SELECT ANY_VALUE(c) AS c, COUNT(1) AS accidents_in_perimeter 
  FROM `project.dataset.cameras` c
  JOIN `project.dataset.accidents` a
  ON c.road = a.street
  AND a.mile BETWEEN c.mile - 0.5 AND c.mile + 0.5
  GROUP BY FORMAT('%t', c)
)   

if to apply to sample data from your question – result is

Row city    state   road    mile    accidents_in_perimeter   
1   Abcd    Wxyz    10101   12.3    2    
2   Efgh    Wxyz    98765   7.8     1