Remove 95 percent of highest count Kusto Query

Question

I am trying to get the total count of a column by referrer and remove the top 95 percent of highest count, and only show the lowest count(5%) of referrer, any examples?

summarize count(id) by referrer where count_id <5%

Answer 1

Here's how you do it (note that in my example I take 50th percentile, just because I don't want to type too much data to have a meaningful 5th percentile :)):

let YourTable = datatable(Name:string, NumCandies:long) [
    "John", 10,
    "Beth", 20,
    "Steven", 30,
    "Paul", 40,
    "Elsa", 50,
    "George", 60,
    "Martha", 70
];
let Percentile50 = toscalar(
    YourTable
    | summarize percentile(NumCandies, 50));
YourTable
| where NumCandies <= Percentile50

This will output:

| Name   | NumCandies |
|--------|------------|
| John   | 10         |
| Beth   | 20         |
| Steven | 30         |
| Paul   | 40         |
|--------|------------|