Fetch top 3 accounts by sales rep by region in Kusto

Question

I'm new to Kusto and I'm trying to fetch top 3 accounts with largest sales amount by sales representative by sales region and I'm struggling to get this right. Imagine a table like this:

Sales_rep	account_id	sales_amount	region
John Smith	1234	$100	North
John Smith	9876	$400	East
John Smith	1729	$1000	North
John Smith	9387	$250	South
Maria Klaus	4567	$300	West
Maria Klaus	7890	$325	South

I want to write a KQL script that returns the top 3 accounts by sales_amount per Sales_rep per region. Pretty easy to fetch in SQL by I'm struggling with KQL. Can you please advice how to work this out? Thank you in advance.

Answer 1

I want to write a KQL script that returns the top 3 accounts by sales_amount per Sales_rep per region

you could use the partition operator.

datatable(Sales_rep: string, account_id: long, sales_amount: double, region: string)
[
    'John Smith',  1234,  100,  'North',
    'John Smith',  9876,  400,  'East',
    'John Smith',  1729,  1000, 'North',
    'John Doe',    19387, 2500, 'North',
    'John Doe',    29387, 2510, 'South',
    'Jane Doe',    39387, 2150, 'South',
    'Jane Doe',    49387, 2750, 'North',
    'Maria Klaus', 4567,  300,  'West',
    'Maria Klaus', 7890,  325,  'South',
    'Santa Klaus', 17890, 3205, 'South',
]
| partition by region ( top 3 by sales_amount desc )

Sales_rep	account_id	sales_amount	region
John Smith	9876	400	East
Jane Doe	49387	2750	North
John Doe	19387	2500	North
John Smith	1729	1000	North
Santa Klaus	17890	3205	South
John Doe	29387	2510	South
Jane Doe	39387	2150	South
Maria Klaus	4567	300	West