Use of Percentile with condition in Kusto

Question

I'm looking to use percentile() in kusto with certain condition here's the data I have,


OnStatus| OffStatus | WebId | UserId |
true    | false     | 1     | A123   |
true    | false     | 2     | A123   |
true    | false     | 3     | A123   |
false   | true      | 4     | A123   |
false   | true      | 1     | B123   |
false   | true      | 2     | B123   |
false   | true      | 3     | B123   |
false   | true      | 4     | B123   |

Here's what I need : unique row for each user with P50 & P95 of the status looking at all the WebIds

Step1:

UserId | P50_StatusOn | P95_StatusOn | P50_StatusOff | P95_StatusOff |
A123   | true         | false        | false         | false         |
B123   | false        | false        | true          | true          |

Step2:

| P50_StatusOnCnt | P95_StatusOnCnt | P50_StatusOffCnt | P95_StatusOffCnt |
| 1               | 0               | 1                |  1               |

score 0 · Answer 1 · answered Oct 08 '21 at 06:54

Here's what you could do for step 1:

let T = datatable(UserId:string, OnStatus:bool, OffStatus:bool)
[
   "A123", true, false,
   "A123", true, false,
   "A123", true, false,
   "A123", false, false,
   "B123", false, true,
   "B123", false, true,
   "B123", false, true,
   "B123", false, true,
];
T
| summarize
    P50_StatusOn = percentile(OnStatus, 50), 
    P95_StatusOn = percentile(OnStatus, 95),
    P50_StatusOff = percentile(OffStatus, 50), 
    P95_StatusOff = percentile(OffStatus, 95)
by UserId

and then for the second step simply add:

| summarize sum(P50_StatusOn), sum(P95_StatusOn), sum(P50_StatusOff), sum(P95_StatusOff)

Use of Percentile with condition in Kusto

1 Answers1