SELECT timestamp in 20 second intervals

• Lord Slaagh

  SSCertifiable

  Points: 5745

  More actions

  January 31, 2017 at 6:54 am

  #321223

  Hello,

  I have been tasked with the assignment of searching for BOTs in our database. I have 1 SQL table that tracks clicks for each report opened based on USERID. So each USERID represents a report click. I need a query that shows me how many clicks happened based on USERID in 20 second intervals, and the clicks need to be greater than 10.

  Example: If UserID = '999' clicked on a report 25 times in 10 seconds, it is most likely a BOT and needs to be in the query. Here is what I have so far, and as always, thank you all in advance!

  SELECT COUNT(UID),datetimestamp, DATEPART(SECOND, datetimestamp)
  FROM TABLE
  WHERE (datetimestamp = '01-01-2016')
  AND DATEPART(SECOND, datetimestamp) >1 AND DATEPART(SECOND, datetimestamp) <21
  GROUP BY UID, datetimestamp

  The are no problems, only solutions. --John Lennon

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 31, 2017 at 7:11 am

  #1925845

  i think your group by needs to round the timestamp to your interval of  20 seconds(DATEPART(SECOND, datetimestamp) %20, i think?)  or at least to the nearest minute, which is a lot easier.

  SELECT COUNT(UID),
   DATEADD(minute, DATEDIFF(minute,0,datetimestamp), 0) AS Roundeddatetimestamp
FROM SomeTable
WHERE (datetimestamp = '01-01-2016')
--AND DATEPART(SECOND, datetimestamp) > 1 AND DATEPART(SECOND, datetimestamp) <21
GROUP BY UID, DATEADD(minute, DATEDIFF(minute,0,datetimestamp), 0)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 31, 2017 at 7:16 am

  #1925847

  In the absence of any table DDL or sample data, here's my guess:

  SELECT
     COUNT(UID)
,    DATEDIFF(second,'20160101',datetimestamp)/20 AS Interval
,    UID
FROM TABLE
WHERE datetimestamp >= '20160101'
AND datetimestamp < '20160102'
GROUP BY
     UID
,    DATEDIFF(second,'20160101',datetimestamp)/20
HAVING COUNT(UID) > 10

• Thom A

  SSC Guru

  Points: 99133

  More actions

  January 31, 2017 at 7:39 am

  #1925859

  How about:
  SELECT COUNT(T.UID),
       DATEADD(second, CASE WHEN DATEPART(second,T.datetimestamp) < 20 THEN 0
                            WHEN DATEPART(second,T.datetimestamp) >= 40 THEN 40
                            ELSE 20
                       END,DATEADD(minute, DATEDIFF(minute,0,T.datetimestamp),0)),
       T.UID
FROM

  T
WHERE CAST(T.datetimestamp AS date) = '20160101'
GROUP BY DATEADD(second, CASE WHEN DATEPART(second,T.datetimestamp) < 20 THEN 0
                              WHEN DATEPART(second,T.datetimestamp) >= 40 THEN 40
                              ELSE 20
                         END,DATEADD(minute, DATEDIFF(minute,0,T.datetimestamp),0)),
         T.UID
HAVING COUNT(T.UID) >= 20;
  I really dislike having to re-align my code every time...
  

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Lord Slaagh

  SSCertifiable

  Points: 5745

  More actions

  January 31, 2017 at 7:54 am

  #1925866

  Thom A - Tuesday, January 31, 2017 7:39 AM

  How about:
  SELECT COUNT(T.UID),
         DATEADD(second, CASE WHEN DATEPART(second,T.datetimestamp) < 20 THEN 0
                              WHEN DATEPART(second,T.datetimestamp) >= 40 THEN 40
                              ELSE 20
                         END,DATEADD(minute, DATEDIFF(minute,0,T.datetimestamp),0)),
         T.UID
  FROM

  T
WHERE CAST(T.datetimestamp AS date) = '20160101'
GROUP BY DATEADD(second, CASE WHEN DATEPART(second,T.datetimestamp) < 20 THEN 0
                              WHEN DATEPART(second,T.datetimestamp) >= 40 THEN 40
                              ELSE 20
                         END,DATEADD(minute, DATEDIFF(minute,0,T.datetimestamp),0)),
         T.UID
HAVING COUNT(T.UID) >= 20;
  I really dislike having to re-align my code every time...
  

  Thank you very much, this worked like a charm!

  The are no problems, only solutions. --John Lennon

• Lord Slaagh

  SSCertifiable

  Points: 5745

  More actions

  January 31, 2017 at 1:31 pm

  #1925952

  Thom A - Tuesday, January 31, 2017 7:39 AM

  How about:
  SELECT COUNT(T.UID),
         DATEADD(second, CASE WHEN DATEPART(second,T.datetimestamp) < 20 THEN 0
                              WHEN DATEPART(second,T.datetimestamp) >= 40 THEN 40
                              ELSE 20
                         END,DATEADD(minute, DATEDIFF(minute,0,T.datetimestamp),0)),
         T.UID
  FROM

  T
WHERE CAST(T.datetimestamp AS date) = '20160101'
GROUP BY DATEADD(second, CASE WHEN DATEPART(second,T.datetimestamp) < 20 THEN 0
                              WHEN DATEPART(second,T.datetimestamp) >= 40 THEN 40
                              ELSE 20
                         END,DATEADD(minute, DATEDIFF(minute,0,T.datetimestamp),0)),
         T.UID
HAVING COUNT(T.UID) >= 20;
  I really dislike having to re-align my code every time...
  

  Thanks Ten! Works perfect.

  The are no problems, only solutions. --John Lennon