Kusto Detective Agency Season 2 のメモ | Case 3

# Kusto Detective Agency Season 2 のメモ 

https://qiita.com/georgeOsdDev@github/items/dc1de56bf8c1a2d7547e 

の続き

Season 2 は 各 Case に挑戦可能になるまで右上のタイマー表示から確認可能。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/820f7154-fcc2-9329-eebb-1e42b97e6b12.png)


また、先着で 4000 人までに Snappy 経由のギフトがもらえます。
> 6. Prizes: Each season has variable amount of prizes. Season 1 distributed 900 prizes. In season 2 there are more than 4,000 prizes worth $25 and $50 dependent on the contest stage, each prize can be claimed via Snappy inc. 

ちなみに内容は $25 = 3,500円 程度のビミョーなカタログから選ぶスタイルでした。

## Case 3 - Return stolen cars!

### 問題

盗まれた車をトラフィックデータを元に探し出す。
車のナンバーは盗難中に付け替えられている。

> We've got an urgent case that needs your expertise! 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.
> 
> Time is of the essence, and we need to find these cars before they are sold or taken out of the city. The police are counting on us to solve this case, and we can't let them down!

## ポイント

* [train for me] のヒントにもあるが join 句を理解して使い分ける必要がある

    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/4829a27b-8cc5-0f14-f17d-3d22c8c1029d.png)

* [Time window join](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/join-timewindow)の考え方で、盗難車両が最後に確認された時間から、該当の住所(`Ave`,`Street`)にて最初の確認された車両を割り出すことで、ナンバーの付替えがある程度推測できる。

* ナンバーが付け替えられた車が最後に確認された住所を割り出す



<details><summary>セットアップ</summary>

```kql
.execute database script <|
// It takes about 1min to script to complete loading the data
.set-or-replace StolenCars <|
datatable(VIN:string)
   ['LG232761G','SA732295L','MW406687Q','PR843817F','EL438126P',
    'GA871473A','IR177866Y','LP489241B','AS483204L','DO255727O',
    'BV850698T','YZ347238C','NJ586451R','VB724416I','SI241398E',
    'IN149152E','PV340883B','CK552050Z','ZJ786806D','KU388194T']
.create-merge table CarsTraffic(Timestamp:datetime, VIN:string, Ave:int, Street:int)
.ingest async into table CarsTraffic(@'https://kustodetectiveagency.blob.core.windows.net/kda2c3cartraffic/log_00000.csv.gz')
.ingest async into table CarsTraffic(@'https://kustodetectiveagency.blob.core.windows.net/kda2c3cartraffic/log_00001.csv.gz')
.ingest into table CarsTraffic(@'https://kustodetectiveagency.blob.core.windows.net/kda2c3cartraffic/log_00002.csv.gz')
```
</details>

<details><summary>回答</summary>

```kql
(
let _lookupWindow=1h;
// 盗難車両が最後に確認された住所
CarsTraffic
| where VIN in (StolenCars)
| summarize last_sighted = arg_max(Timestamp,*) by VIN
// 時間枠で1分毎のキーを作る
| extend TimeKey = range(bin(last_sighted, 1min), last_sighted + _lookupWindow, 1min) 
| mv-expand TimeKey to typeof(datetime) 
| join kind = inner (
    //該当の住所で最初に確認された車両(X)の一覧
    CarsTraffic
        | summarize first_sighted=arg_min(Timestamp,*) by VIN
        | extend TimeKey = bin(first_sighted, 1min) 
    ) on TimeKey, Ave, Street
)| join kind = rightsemi
// x が最後に確認された住所の一覧
(CarsTraffic
    | summarize arg_max(Timestamp,*) by VIN
) on $left.VIN1 == $right.VIN
| summarize count() by Street, Ave
| order by count_ desc
```
</details>