KDA: Echoes of Deception - Case 3

The crime spree in Digitown is not ending. This time 20 cars have been stolen and we need to find where they’re stored.

The problem 🔗︎

This is the abridged case description. Emphasis mine:

There has been a sudden increase in unsolved cases of stolen cars all across our city, and the police need our help again to crack the case.

We’ve been given access to a massive dataset of car traffic for over a week, as well as a set of cars that have been stolen. It’s possible that the car’s identification plates were replaced during the robbery, which makes this case even more challenging.

We need you to put on your detective hat and analyze the data to find any patterns or clues that could lead us to the location of these stolen cars. It is very likely that all the stolen cars are being stored in the same location.

And the main question:

Where are the stolen cars being kept?

More specifically: Avenue and Street numbers.

Investigation 🔗︎

We have two tables - StolenCars and CarsTraffic. StolenCars is just a list of 20 VINs, so let’s take a look at CarsTraffic.

CarsTraffic
| take 50

So it looks to be VINs and their respective location at a point in time. I think the main clue is that they’ve changed the license plates so this seems to be another case of a self-join.

Solution 🔗︎

• Find the last location (Ave and Street) and time of each stolen VIN
• Find new possible VINs - join on the last location within a certain timeframe - let’s say 10 minutes
• Find last location of the new possible VINs
• Aggregate by location and see if any of them has around 20 cars

let LastLocation = 
(
    CarsTraffic
    | lookup kind = inner StolenCars on VIN
    | summarize arg_max(Timestamp, *) by VIN
    | project-rename
        VinChangeTime = Timestamp
    | distinct Ave, Street, VinChangeTime
);
let NewPossibleVINs = 
(
    CarsTraffic
    | lookup kind=inner LastLocation on Ave, Street
    | where Timestamp >= VinChangeTime and Timestamp < VinChangeTime + 10m // VIN change within 10 minutes 
    | distinct VIN
);
NewPossibleVINs
| join kind = inner CarsTraffic on VIN
| summarize arg_max(Timestamp, *) by VIN
| summarize count() by Ave, Street
| where count_ >= 20
| top 5 by count_ desc 

Let’s input it as an answer and grab another batch.

Thank you for reading.