cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

USQL query or number of session by user

Mahdi_zineddine
Visitor

Hi dears, 
I have an USQL query where I am showing by user, the session duration, and the number of actions by users, but I would like to add : the number of visits (sessions) by user also 

 

 

SELECT userId, duration/1000/60 AS "Session duration / min", userActionCount AS "Number of actions" FROM usersession WHERE (userType="REAL_USER") AND userId IS NOT NULL

 

 

 Can you help please ?

 

Regards 

2 REPLIES 2

AhmedKhaled_94
Observer

SELECT
userId,
COUNT(userId) AS "Number of visits",
AVG(duration/1000/60) AS "Average session duration / min",
SUM(userActionCount) AS "Total number of actions"
FROM
usersession
WHERE
userType = "REAL_USER"
AND userId NOT LIKE "%ca-cib.com%"
AND userId NOT LIKE "%zarrad%"
AND userId NOT LIKE "%aydi%"
AND userId IS NOT NULL
GROUP BY
userId;

Does this help?

Certified Dynatrace Associate - Senior APM Engineer - SRE/DevOps

Mahdi_zineddine
Visitor

I modified it as bellow because I can not calculate the AVG in seconds 

I had the error : Mathematical Operation with aggregator functions is not supported!

 

SELECT
  userId,
  COUNT(*) AS "Number of visits",
  AVG(duration) AS "Average session duration in ms",
  SUM(userActionCount) AS "Total number of actions"
FROM
  usersession
WHERE
  userType = "REAL_USER"
  AND userId IS NOT NULL
GROUP BY
  userId

 

 

 is there any solution for the ms to seconds ? 

Featured Posts