Summarize a Kusto Table based on max_count

Question

I have data in large table as follows

I would like to summarize in the following manner in Kusto. So in the above session A ends at PageId =5, session B ends at PageId=3, session C ends at PageId=2, session D ends at PageId=2. Hence the summarized table would be as below.

However, I am unable to figure out how to do it. Any tips on how to go about this - I tried summarize by maxif() but that didn't seem to work.

Answer 1

you could try something like this

let T = datatable(Session:string, PageId:long)
[
    "A", 1,
    "A", 2,
    "A", 3,
    "A", 4,
    "A", 5,
    "B", 1,
    "B", 2,
    "B", 3,
    "C", 1,
    "C", 2,
    "D", 1,
    "D", 2,
]
;
let session_ids = T | distinct PageId;
session_ids
| join kind=leftouter (
    T
    | summarize PageId = max(PageId) by Session
    | summarize count() by PageId
) on PageId
| extend count_ = coalesce(count_, 0)
| project PageId, count_
| order by PageId asc

PageId	count_
1	0
2	2
3	1
4	0
5	1