We have mileStoneID as Create_Cart, Checkout, Create_Checkout, Web_order_create and Order_Release.
I have a very interesting requirement, where I need show count of pddID by mileStoneID with a stash intervals < 1min 1min-3mins 3-5mins >5mins . Let say pddID = 12345687 is in mileStoneId Web_order_create and it's been in that mileStoneId for 2mins, it should fall in the count bucket of create and stash interval of 1min-3mins and pddID = 12345687 should not be included in another mileStoneId count.
Please feel free to ask any questions,Thanks for your time.
| stats latest(mileStoneId) as mileStoneId, latest(_time) as _time by pddID
| eval stash_interval=now()-_time
| eval interval_bucket=case(stash_interval<60,"< 1min", stash_interval<180,"1-3min", stash_interval<300,"3-5min",1==1,">5mins")`
| stats count by interval_bucket, mileStoneId
First, get all the data you need as part of your base search. Then get the latest mileStoneID and _time for each pddID.
Assuming that the _time of the event is correct, you could calculate the how long it has been in that state by doing eval stash_interval=now()-_time .
Next, you can calculate what interval "bucket" you want to put it in, with eval interval_bucket=case(stash_interval<60,"< 1min", stash_interval<180,"1-3min", stash_interval<300,"3-5min",1==1,">5mins")
Once you have that data, you can do a stats count by mileStoneId, interval bucket, and you should end up with the table you are after.