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