Query Details
Top N By Group Example Via Top Nested Option 2
Query
let SampleUserData=datatable (UserId:int, OperationDate:datetime)
[1, datetime(2018-01-01 12:30:00),
1, datetime(2018-01-01 13:30:00),
2, datetime(2018-01-02 12:30:00),
2, datetime(2018-01-03 13:30:00),
3, datetime(2018-01-02 12:30:00),
3, datetime(2018-01-01 12:30:00),
3, datetime(2018-02-02 13:30:00),
1, datetime(2018-02-02 12:30:00),
1, datetime(2018-02-03 12:30:00),
3, datetime(2018-03-01 12:30:00),
3, datetime(2018-03-02 12:30:00),
2, datetime(2018-03-02 12:30:00),
1, datetime(2018-03-03 11:30:00),
1, datetime(2018-03-03 12:30:00),
1, datetime(2018-03-03 13:30:00)
];
let SampleUserData2 =
SampleUserData
| extend MonthNum = datetime_part("Month", OperationDate);
SampleUserData2
| top-nested toscalar(SampleUserData2 | summarize dcount(MonthNum)) of MonthNum by max(1), top-nested 2 of UserId by count()
Explanation
The query is creating a table called SampleUserData with two columns: UserId (integer) and OperationDate (datetime). It then creates another table called SampleUserData2 by extending the first table with a new column called MonthNum, which extracts the month from the OperationDate column. Finally, it returns the top two users with the highest count of unique months they performed an operation, along with the count of operations they performed.
Details
Rod Trent
Released: March 26, 2020
Tables
SampleUserDataSampleUserData2
Keywords
SampleUserData,SampleUserData2,UserId,OperationDate,MonthNum
Operators
datatableextenddatetime_parttop-nestedtoscalarsummarizedcountmaxcount