The Case

It’s Kusto Detective Agency time again! And this time it’s Lieutenant Laughter who needs our help. Serously? Lieutenant Laughter? Sounds sketchy, I’ve a feeling LL is going to turn out to be Professor Smoke.

So our problem is to find the detective who made the most money in 2022.

One nice addition to this year is the “Train Me” button which gives some great examples of how to explore the data. Train Me Pretty cool!

Solving

First, let’s have a look at the overall shape of the data:

DetectiveCases
   | summarize Count=count() by EventType
   | render barchart

So here we can see around 100K cases opened, which are assigned to multiple detectives. There are nearly ~400K solved case events, so many cases are solved by multiple detectives.

How many?

DetectiveCases
    | where EventType == 'CaseSolved'
    | summarize count_distinct(CaseId), count()

113K distinct cases solved, 381K total cases solved events. So some cases are solved by multiple detectives.

I’m sure there’s a better way to do a count of counts but hey…

DetectiveCases
    | where EventType == 'CaseSolved'
    | summarize Count=count() by CaseId
    | summarize NumberofSolves=count() by Count
    | render columnchart

What’s the Detective Agency Bounty Budget?

DetectiveCases
    | summarize sum(toreal(Properties.Bounty))

$556M. That’s a lot of donuts.

OK enough exploring, lets solve this.

We know many cases are solved by multiple detectives. Let’s assume the detective with the first solution takes the all the $$$. First, we’ll expand the Bounty then find the first detective to solve each case. Then we’ll sum up the bounties for each detective and winner winner chicken dinner.

DetectiveCases
    | where EventType == 'CaseOpened'
    | extend Bounty = toreal(Properties.Bounty)
    | project CaseId, Bounty
    | join kind=inner (
        DetectiveCases
        | where EventType == 'CaseSolved'
        | summarize CaseSolved=arg_min(Timestamp, DetectiveId) by CaseId
    ) on CaseId
    | summarize TotalEarnings=sum(Bounty) by DetectiveId
    | top 1 by TotalEarnings

So kvc12a22e9e9e65c1694f1 made a smooth $385K in 2022. Nice.