USQL: number of user sessions that performed a particular action X times

[Weilin1]

Hi, 

I would like to check if there is any way i am able to create a USQL query to select count of usersessions that has completed a particular action or event more than once (best if can i dictate the number of times they completed this action or event)?

Thanks in advance.

[Ana_Kuzmenchuk]

Hi @Weilin1, this document may be a good start at cracking your use case ⚡ please let me know if it was helpful or if you have already figured it out on your own 😉 the Community would be grateful if you shared

[Weilin1]

Hi @Ana_Kuzmenchuk, thanks for reaching out. I had explored the document before but i haven't encounter any that fits my use case 😔

My guess is it will need require 2 select statements to query out the result, but this is currently not feasible. 

[Julius_Loman · Accepted solution]

@Weilin1 the closest I was able to achieve is this - replace <actionname> with your desired action name (repeated multiple times) and <application> with the application name.

select 
    usersession.userSessionId as 'userSessionID'
    , condition(count(name), where name="<actionname>") filter >1 as 'repeated actions'
from 
    useraction
where 
    application="<application>" 
    and name="<actionname>"
group by 
    usersession.userSessionId

Unfortunately, I was not able to count it on session level - such as the number of sessions including a particular action multiple times.

[Weilin1]

I see,  yes i have been using a similar statement as well. 

Select count(name) Filter >1 as "Attempt Count", top(usersession.userSessionId, 1000) as "Session ID" from useraction where useraction.name = "<actionName>" group by usersession.userSessionId order by count(name) desc

Seems like exporting the data out and query might be the way to go? 

Thanks

[Julius_Loman]

That's correct at this time. There will be improvements with Grail soon, but for Managed you will need to do this externally. Either by leveraging sessions export or you can query that using chunks of data, so you won't hit the limits. A