Query Details
Top N By Group Example Via LAG Option 1
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)
];
SampleUserData
| extend MonthNum = datetime_part("Month", OperationDate)
| summarize CountByMonthNumUserId = count() by MonthNum,UserId
| order by MonthNum asc, UserId asc, CountByMonthNumUserId desc
| extend RowNum = row_number(1, prev(MonthNum) != MonthNum)
| extend CountIsSameAsPrev = CountByMonthNumUserId == prev(CountByMonthNumUserId)
| where RowNum in (1,2) or CountIsSameAsPrev
| project-away RowNum, CountIsSameAsPrev
Explanation
The query is analyzing a dataset called SampleUserData, which contains information about user IDs and operation dates.
The query first extends the dataset to include a new column called MonthNum, which extracts the month from the OperationDate column.
Then, it summarizes the data by counting the number of occurrences for each combination of MonthNum and UserId.
The results are then ordered in ascending order by MonthNum, UserId, and the count of occurrences.
Next, the query extends the dataset to include a new column called RowNum, which assigns a row number to each row based on whether the previous MonthNum is different from the current MonthNum.
Another column called CountIsSameAsPrev is added to check if the count of occurrences is the same as the previous row.
The query then filters the results to only include rows where RowNum is either 1 or 2, or where CountIsSameAsPrev is true.
Finally, the query removes the RowNum and CountIsSameAsPrev columns from the final result.
Details
Rod Trent
Released: March 26, 2020
Tables
Keywords
Operators