Search code examples
sqlsql-serveranalytics

Calculate the individual user time on my site?

Need to calculate the average time a user spends on my site. Below is how we store each session against each unique uid.

Session status 1 means that the session is complete..and if it is 0 then the session is still active/user didn't logout.

Any suggestions on what approach to use?

Usersession 

rowid   uid     session     createddatetime     SessionStatus   LastModifieddatetime    LastAccesseddatetime
226636  22      D958DFC3    02-03-2018 11:41:56     1           02-03-2018 11:43:07     02-03-2018 11:41:56
226635  22      67AABA7B    02-03-2018 11:40:16     1           02-03-2018 11:40:55     02-03-2018 11:40:16
218830  5048    1E1D7790    26-02-2018 07:04:29     1           26-02-2018 07:04:53     26-02-2018 07:04:29
218829  5048    0EB86803    26-02-2018 07:02:54     0                    NULL           26-02-2018 07:02:54
Solution

  • If I get it correctly, LastModifiedDate is the "logout" status, so:

    SELECT UserID, AVG(Datediff("minute", LastAccessDateTime, LastModifiedDate))
FROM table    
WHERE SessionStatus = 1    
GROUP BY UserID