Priority Sorting on a column Kusto Query

Question

Scenario: Players can be marked with the status winner, tied, or loser. The priorities of the status is as such: If a player is ever "tied", they can't be a "loser", if they are ever a "winner", they can't be a "loser" or "tied". Table 1 has error rows recorded but the desired table should display the players and their up-to-date status and the timestamp of the most recent correct records. Is this something that is doable with Kusto? I can select the most recent by timestamp with the query below. I am unsure how to compare. I've started looking at the prev() function but I'm not certain this will do what I want in terms of comparing based on a priority. (https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/prevfunction)

          let T1 = datatable(player:string, status:string, timestamp:datetime)
        [
            "A", "winner", datetime(2020-11-24 08:00),
            "A", "winner", datetime(2020-11-24 10:00),
            "B", "tied", datetime(2020-11-24 09:00),
            "B", "tied", datetime(2020-11-24 11:00),
            "B", "tied", datetime(2020-11-24 14:00),
            "B", "loser", datetime(2020-11-24 15:00),
            "C", "loser", datetime(2020-11-24 08:00),
            "C", "loser", datetime(2020-11-24 10:00),
            "C", "loser", datetime(2020-11-24 11:00),
            "C", "loser", datetime(2020-11-24 13:00),
            "C", "tied", datetime(2020-11-24 14:00),
            "C", "winner", datetime(2020-11-24 15:00),
            "D", "winner", datetime(2020-11-24 07:00),
            "D", "winner", datetime(2020-11-24 11:00),
            "D", "winner", datetime(2020-11-24 16:00),
            "D", "tied", datetime(2020-11-24 21:00),
            "E", "tied", datetime(2020-11-24 09:00),
            "E", "tied", datetime(2020-11-24 11:00),
            "E", "loser", datetime(2020-11-24 13:00),
            "E", "tied", datetime(2020-11-24 18:00),
            "F", "loser", datetime(2020-11-24 10:00),
            "F", "loser", datetime(2020-11-24 11:00),
            "F", "loser", datetime(2020-11-24 18:00),
            "G", "loser", datetime(2020-11-24 11:00),
            "G", "tied", datetime(2020-11-24 14:00),
            "G", "loser", datetime(2020-11-24 16:00),
            "G", "tied", datetime(2020-11-24 18:00),
            "G", "loser", datetime(2020-11-24 21:00),
        ]
        ;
        T1
        | summarize arg_max(timestamp, *) by player
        | order by player asc
            
    result:
    player| timestamp                   | status
    _____________________________________________
    A     | 2020-11-24 10:00:00.0000000 | winner
    B     | 2020-11-24 15:00:00.0000000 | loser 
    C     | 2020-11-24 15:00:00.0000000 | winner
    D     | 2020-11-24 21:00:00.0000000 | tied
    E     | 2020-11-24 18:00:00.0000000 | tied
    F     | 2020-11-24 18:00:00.0000000 | loser
    G     | 2020-11-24 21:00:00.0000000 | loser
    H     | 2020-11-24 21:00:00.0000000 | loser

final desired result table:
    player| timestamp                   | status
    _____________________________________________
    A     | 2020-11-24 10:00:00.0000000 | winner
    B     | 2020-11-24 14:00:00.0000000 | tied 
    C     | 2020-11-24 15:00:00.0000000 | winner
    D     | 2020-11-24 16:00:00.0000000 | winner
    E     | 2020-11-24 18:00:00.0000000 | tied
    F     | 2020-11-24 18:00:00.0000000 | loser
    G     | 2020-11-24 18:00:00.0000000 | tied
    H     | 2020-11-24 09:00:00.0000000 | winner

Yoni L. Yoni L. · Accepted Answer · 2020-12-01T20:20:39

If I understand your question correctly, the following could work.

Calculate the aggregated status per player, according the logic you've provided (using make_set() and array_index_of().
Find the max. timestamp per player/state, using a join

let T = 
    datatable(player: string, status: string, timestamp: datetime)
    [
        "A", "winner", datetime(2020-11-24 08:00),
        "A", "winner", datetime(2020-11-24 10:00),
        "B", "tied", datetime(2020-11-24 09:00),
        "B", "tied", datetime(2020-11-24 11:00),
        "B", "tied", datetime(2020-11-24 14:00),
        "B", "loser", datetime(2020-11-24 15:00),
        "C", "loser", datetime(2020-11-24 08:00),
        "C", "loser", datetime(2020-11-24 10:00),
        "C", "loser", datetime(2020-11-24 11:00),
        "C", "loser", datetime(2020-11-24 13:00),
        "C", "tied", datetime(2020-11-24 14:00),
        "C", "winner", datetime(2020-11-24 15:00),
        "D", "winner", datetime(2020-11-24 07:00),
        "D", "winner", datetime(2020-11-24 11:00),
        "D", "winner", datetime(2020-11-24 16:00),
        "D", "tied", datetime(2020-11-24 21:00),
        "E", "tied", datetime(2020-11-24 09:00),
        "E", "tied", datetime(2020-11-24 11:00),
        "E", "loser", datetime(2020-11-24 13:00),
        "E", "tied", datetime(2020-11-24 18:00),
        "F", "loser", datetime(2020-11-24 10:00),
        "F", "loser", datetime(2020-11-24 11:00),
        "F", "loser", datetime(2020-11-24 18:00),
        "G", "loser", datetime(2020-11-24 11:00),
        "G", "tied", datetime(2020-11-24 14:00),
        "G", "loser", datetime(2020-11-24 16:00),
        "G", "tied", datetime(2020-11-24 18:00),
        "G", "loser", datetime(2020-11-24 21:00),
    ]
;
T
| summarize make_set(status) by player
| project player, status = case(array_index_of(set_status, "winner") > -1, "winner",
                                array_index_of(set_status, "tied") > -1, "tied",
                                "loser")
| join (
    T
    | summarize timestamp = max(timestamp) by player, status
) on player, status
| project player, timestamp, status

Priority Sorting on a column Kusto Query

1 Answers