jeudi 26 juillet 2018

In SQL, is there a way to count something only if there is no activity for 30 minutes?

I am currently trying to figure out a way to count website visits using SQL. We have a database that records when a user accesses the website. I am trying to count a visit as an initial connect, and then inactivity for 30 minutes. Every time activity occurs, the "timer" is reset, but once 30 minutes have passed without a connect, I was it to count as a visit. I also need this sorted by week and by user. This is what I currently have:

SELECT
CONVERT(VARCHAR(10), DATEADD(WK, WK, 0), 101) AS 'weekstart'
, user_id
, cnt FROM (
SELECT
    DATEDIFF(DD, 0, INSERT_DTTM)/7 AS WK
    , COUNT(*) AS cnt
    , RESOURCE_ID AS user_id
FROM CATO.dbo.ACR_METRICS_VIEW
WHERE ENVIRONMENT = 'PRODUCTION'
   AND (BUSINESS_UNIT <> 'OFFICE' OR BUSINESS_UNIT IS NULL)
   AND INSERT_DTTM >='2018-01-01'
GROUP BY DATEDIFF(DD, 0, INSERT_DTTM)/7, RESOURCE_ID) AS X ORDER BY weekstart

That sorts it by week and user id. I just need to add the number of visits. I can get total number of page views by doing a simple COUNT(*), but I am looking for visits as defined above. Is there a way to do this in SQL?

Aucun commentaire:

Enregistrer un commentaire