Case Three - Bank Job

The Case

This week, Mr Knuckles, Mr Lenin (or was it Dirty Dick and Fingers all along?) and an unknown accomplice are on the run and it’s our job to find out where they’re hiding out.

Solving

First, let’s find all the cars in the area during the approximate time of the robbery. One block each way should be enough:

Traffic 
| where 
    Ave between (156 .. 158) and Street between (146 .. 148)
    and Timestamp between( datetime(2022-10-16T08:15) .. datetime(2022-10-16T08:35) )
| distinct VIN

..then look for the final location of each car after the heist. Witnesses said they left in separate cars so the spot with three cars is where the gang is hiding out.

We join our list of suspect VINs back to the Traffic table, find the max Timestamp after the robbery for each and summarize by location.

Traffic 
| where 
    Ave between (156 .. 158) and Street between (146 .. 148)
    and Timestamp between(datetime(2022-10-16T08:15) .. datetime(2022-10-16T08:35) )
| distinct  VIN
| join Traffic on VIN
| where Timestamp > datetime(2022-10-16T08:31)
| summarize arg_max(Timestamp, *) by VIN
| summarize Count=count() by Ave, Street
| where Count > 2

Busted.

“It’s no good, Fingers! These kids are far too clever for us! We’ll get 15 years each for this! Oh well, I suppose I’d better go down to the police station and get nicked, then.”