Summarize and Include Column Value

Question

I'm new to KQL and thought going through the Kusto Detective Agency training would help. Being so new I'm stuck on the first question and can't figure out how to get past a certain point. I'm not even sure the correct terminology for my question but I've gotten to this point

DetectiveCases
| where EventType == 'CaseOpened'
| project CaseOpened = Timestamp, CaseId, Bounty = toreal(Properties.Bounty)
| join kind=innerunique 
    (
    DetectiveCases
    | where EventType == 'CaseSolved'
    | summarize FirstClosed=min(Timestamp) by CaseId)
    //| summarize FirstClosed=min(Timestamp) by CaseId, DetectiveId)
    on CaseId

That gets me a table with unique CaseId's, their Bounty and the date of the first closed case. I need to find the detective who made the most bounty and this is where I'm stuck, I can't figure out how to include the DetectiveId for only the first closed case. If I use the commented out line instead of the one above it then I get duplicate CaseId's for every Detective that solved the case. Only the first Detective that solves a case gets the bounty.

I need the detective included because then I believe I can add this to get the query to get total bounties for each detective and find the answer.

| summarize TotalBounty=sum(Bounty) by DetectiveId
| sort by TotalBounty desc

How do I include the DetectiveId, so that I can use it after the join, but only for the first solved case?

Answer 1

Since this is a game, and I don't want to ruin your fun I won't tell you the answer :)

You should look into arg_min and arg_max which directly answers your original question about getting the value of a different column than the one being maximized (or minimized).

Copying the example from the docs:

StormEvents 
| summarize arg_max(BeginLat, BeginLocation) by State

This gives you the BeginLocation of the maxium BeginLat by State (the example is somewhat confusing but you get the point...)

Also a small tip: use let (see docs) to better separate logic and prevent long, nested queries.

Per your example,

let CaseBounties = DetectiveCases
| where EventType == 'CaseOpened'
| extend Bounty = toreal(Properties.Bounty)
| project CaseId, Bounty;
DetectiveCases
| where EventType == 'CaseSolved'
| summarize FirstClosed=min(Timestamp) by CaseId)
| join CaseBounties on CaseId
...