Kusto query to calculate number of users who experienced X number of crashes in Y days since release

Question

My kusto data table records an event any time someone using the product experiences a crash. I want to calculate how many users experienced at least "X" number of crashes in "Y" number of days since the product was released.

So if the product was released on January 1st, and the table of crash events looks like:

 |  Date  |  User Id  |  Days Since Release  |
 |  1/1   |    A      |          0           |
 |  1/1   |    A      |          0           |
 |  1/1   |    B      |          0           |
 |  1/2   |    A      |          1           |
 |  1/3   |    B      |          2           |
 |  1/4   |    C      |          3           |

Then the results would be:

 | Days Since Release  |  Number of Crashes  | Number of users  |
 |        0            |         1           |        2         |    // Users A and B experienced 1 crash on 0th day
 |        0            |         2           |        1         |    // User A experienced 2 crashes on 0th day
 |        1            |         1           |        2         |    // Users A and B experienced at least 1 crash on 1st day
 |        1            |         2           |        1         |    // User A experienced at least 2 crashes on 1st day
 |        1            |         3           |        1         |    // User A experienced at least 3 crashes on 1st day
 |        2            |         1           |        2         |    // Users A and B experienced at least 1 crash on 2nd day
 |        2            |         2           |        2         |    // Users A and B experienced at least 2 crashes on 2nd day
 |        2            |         3           |        1         |    // User A experienced at least 3 crashes on 2nd day
 |        3            |         1           |        3         |    // Users A, B and C experienced at least 1 crash on 3rd day
 |        3            |         2           |        2         |    // Users A and B experienced at least 2 crashes on 3rd day
 |        3            |         3           |        1         |    // User A experienced at least 3 crashes on 3rd day

I tried to do this using the activity_engagement function but have not been able to perform the aggregations needed on number of crashes "X" and days since release "Y".

Answer 1

Please see the query below, I believe this is what you're looking for. The idea is to expand the counts for each user from the day of the crash and until the total DaysSinceRelease (since if a crash happened on day #1, it should also be considered in all days following #1). Then, we also expand range(1, NumCrashes, 1) since if a user had 3 crashes, these should also be counted in the at-least-1 and at-least-2 bins.

let totalDaysSinceRelease = 3; 
datatable(Date:datetime, UserId:string, DaysSinceRelease:long)
[ 
    datetime(2020-01-01), "A", 0,
    datetime(2020-01-01), "A", 0, 
    datetime(2020-01-01), "B", 0, 
    datetime(2020-01-02), "A", 1, 
    datetime(2020-01-03), "B", 2,
    datetime(2020-01-04), "C", 3 
]
| summarize NumCrashes = count() by UserId, DaysSinceRelease
| order by UserId asc, DaysSinceRelease asc
| extend NumCrashes = row_cumsum(NumCrashes, UserId != prev(UserId))
| extend DaysSinceRelease = range(DaysSinceRelease, totalDaysSinceRelease, 1)
| mv-expand DaysSinceRelease to typeof(long)
| summarize NumCrashes=max(NumCrashes) by UserId, DaysSinceRelease
| extend NumCrashes = range(1, NumCrashes, 1) | mv-expand NumCrashes to typeof(long)
| summarize dcount(UserId) by DaysSinceRelease, NumCrashes
| order by DaysSinceRelease asc, NumCrashes asc