sortingkqlazure-data-explorerrow-number

Fetch top 3 accounts by sales rep by region in Kusto


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.


Solution

  • 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