February 7, 2017 at 10:43 am
Hello SSC,
I posted this question earlier yesterday, but I don't think I asked the question correctly, so I will try again...
I have a table with two columns UID and Timestamp. I am trying to find BOTs in our system. So we’re looking for at least one case, per user, per day, (user is UID), where the difference between 2 consecutive timestamps is greater than 4 hours.
It’s really easy to do in Excel, so maybe someone knows how to implement this idea in SQL. I know you guys like to ask for data, but this is a simple table with 2 columns, UID and timestamp, so it should be relatively easy. I was able to get the time down to clicks per minute in the query below, but as helpful as that query was, my boos is now asking for the table below.
| uid | Hit timestamp | Previous timestamp (also can use next timestamp) | Difference in hours | Rolling Status |
| U1 | 2pm | - | - | - |
| U1 | 3pm | 2pm | 1 | Might be bot |
| U1 | 4pm | 3pm | 1 | Might be bot |
| U1 | 10pm | 4pm | 6 | Not bot |
| U2 | 8am | - | - | |
| U2 | 5pm | 8am | 9 | Not bot |
| U2 | 11pm | 5pm | 6 | Not bot |
QUERY FOR HITS PER MINUTE
select count(*) as hits, left(convert(varchar, timestamp,121), 16) as minte, uid
from TABLE
where timestamp > '2016-02-01'
and timestamp <= '2016-02-02'
and uid <> '-'
group by uid, left(convert(varchar, timestamp,121), 16) --minute
order by 1 desc
The are no problems, only solutions. --John Lennon
February 7, 2017 at 11:00 am
Lord Slaagh - Tuesday, February 7, 2017 10:43 AMHello SSC,I posted this question earlier yesterday, but I don't think I asked the question correctly, so I will try again...
I have a table with two columns UID and Timestamp. I am trying to find BOTs in our system. So we’re looking for at least one case, per user, per day, (user is UID), where the difference between 2 consecutive timestamps is greater than 4 hours.
It’s really easy to do in Excel, so maybe someone knows how to implement this idea in SQL. I know you guys like to ask for data, but this is a simple table with 2 columns, UID and timestamp, so it should be relatively easy. I was able to get the time down to clicks per minute in the query below, but as helpful as that query was, my boos is now asking for the table below.
uid Hit timestamp Previous timestamp (also can use next timestamp) Difference in hours Rolling Status U1 2pm - - - U1 3pm 2pm 1 Might be bot U1 4pm 3pm 1 Might be bot U1 10pm 4pm 6 Not bot U2 8am - - U2 5pm 8am 9 Not bot U2 11pm 5pm 6 Not bot QUERY FOR HITS PER MINUTE
select count(*) as hits, left(convert(varchar, timestamp,121), 16) as minte, uid
from TABLE
where timestamp > '2016-02-01'
and timestamp <= '2016-02-02'
and uid <> '-'
group by uid, left(convert(varchar, timestamp,121), 16) --minute
order by 1 desc
Easy though it may be, in order for someone here to be able to write the query, test it and give you the solution, some data is required. If you're not going to do it, they have to do it. Do you think that's reasonable?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 7, 2017 at 11:18 am
Here is the data.
CREATE TABLE #test
(UID varchar(50),
DateTimeStamp timestamp
);
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 14:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 14:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 15:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 15:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 16:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 16:08:49.417'
INSERT INTO #test 'AJudge@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'AJudge@yanks.com', '2017-02-06 16:08:49.417'
INSERT INTO #test 'AJudge@yanks.com', '2017-02-06 18:08:49.417'
INSERT INTO #test 'BigSloppy@BostonSux.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'GSanchez@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 14:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 15:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 16:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 13:09:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 17:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 20:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 23:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 12:08:49.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:50.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:51.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:52.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:53.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:54.417'
The are no problems, only solutions. --John Lennon
February 7, 2017 at 11:28 am
Lord Slaagh - Tuesday, February 7, 2017 11:18 AMHere is the data.CREATE TABLE #test
(UID varchar(50),
DateTimeStamp timestamp
);INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 14:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 14:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 15:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 15:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 16:08:49.417'
INSERT INTO #test 'DJeter@yanks.com', '2017-02-06 16:08:49.417'INSERT INTO #test 'AJudge@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'AJudge@yanks.com', '2017-02-06 16:08:49.417'
INSERT INTO #test 'AJudge@yanks.com', '2017-02-06 18:08:49.417'INSERT INTO #test 'BigSloppy@BostonSux.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'GSanchez@yanks.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 14:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 15:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 16:08:49.417'
INSERT INTO #test 'GSanchez@ yanks.com', '2017-02-06 13:09:49.417'INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 17:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 20:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 23:08:49.417'
INSERT INTO #test 'CBeltran@Texas.com', '2017-02-06 12:08:49.417'INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:49.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:50.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:51.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:52.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:53.417'
INSERT INTO #test 'MMoore@SFG.com', '2017-02-06 13:08:54.417'
Your code contains an invalid data type and every row has invalid syntax. Please make sure your code at least runs in future🙂
if Object_Id('tempdb..#test','U') is not null
drop table #test;
CREATE TABLE #test
(
UID VARCHAR(50)
, DateTimeStamp DATETIME
);
INSERT INTO #test
VALUES
(
'DJeter@yanks.com', '2017-02-06 13:08:49.417'
)
,(
'DJeter@yanks.com', '2017-02-06 13:08:49.417'
)
,(
'DJeter@yanks.com', '2017-02-06 14:08:49.417'
)
,(
'DJeter@yanks.com', '2017-02-06 14:08:49.417'
)
,(
'DJeter@yanks.com', '2017-02-06 15:08:49.417'
)
,(
'DJeter@yanks.com', '2017-02-06 15:08:49.417'
)
,(
'DJeter@yanks.com', '2017-02-06 16:08:49.417'
)
,(
'DJeter@yanks.com', '2017-02-06 16:08:49.417'
)
,(
'AJudge@yanks.com', '2017-02-06 13:08:49.417'
)
,(
'AJudge@yanks.com', '2017-02-06 16:08:49.417'
)
,(
'AJudge@yanks.com', '2017-02-06 18:08:49.417'
)
,(
'BigSloppy@BostonSux.com', '2017-02-06 13:08:49.417'
)
,(
'GSanchez@yanks.com', '2017-02-06 13:08:49.417'
)
,(
'GSanchez@ yanks.com', '2017-02-06 14:08:49.417'
)
,(
'GSanchez@ yanks.com', '2017-02-06 15:08:49.417'
)
,(
'GSanchez@ yanks.com', '2017-02-06 16:08:49.417'
)
,(
'GSanchez@ yanks.com', '2017-02-06 13:09:49.417'
)
,(
'CBeltran@Texas.com', '2017-02-06 13:08:49.417'
)
,(
'CBeltran@Texas.com', '2017-02-06 17:08:49.417'
)
,(
'CBeltran@Texas.com', '2017-02-06 20:08:49.417'
)
,(
'CBeltran@Texas.com', '2017-02-06 23:08:49.417'
)
,(
'CBeltran@Texas.com', '2017-02-06 12:08:49.417'
)
,(
'MMoore@SFG.com', '2017-02-06 13:08:49.417'
)
,(
'MMoore@SFG.com', '2017-02-06 13:08:50.417'
)
,(
'MMoore@SFG.com', '2017-02-06 13:08:51.417'
)
,(
'MMoore@SFG.com', '2017-02-06 13:08:52.417'
)
,(
'MMoore@SFG.com', '2017-02-06 13:08:53.417'
);
SELECT * FROM #test t
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 7, 2017 at 11:31 am
Sorry man, I am doing 4 things a once here. Thank you for cleaning that up.
The are no problems, only solutions. --John Lennon
February 7, 2017 at 11:40 am
Something like this?
WITH TEMP_CTE AS(
SELECT *, LAG(DateTimeStamp, 1) OVER(PARTITION BY UID ORDER BY DateTimeStamp ASC) AS PREVIOUS_DATE FROM #test
)
SELECT UID, DateTimeStamp, PREVIOUS_DATE, DATEDIFF(hour, PREVIOUS_DATE, DateTimeStamp) FROM TEMP_CTE
ORDER BY UID, DateTimeStamp ASC
February 7, 2017 at 2:13 pm
Lord Slaagh - Tuesday, February 7, 2017 10:43 AMHello SSC,I posted this question earlier yesterday, but I don't think I asked the question correctly, so I will try again...
I have a table with two columns UID and Timestamp. I am trying to find BOTs in our system. So we’re looking for at least one case, per user, per day, (user is UID), where the difference between 2 consecutive timestamps is greater than 4 hours.
It’s really easy to do in Excel, so maybe someone knows how to implement this idea in SQL. I know you guys like to ask for data, but this is a simple table with 2 columns, UID and timestamp, so it should be relatively easy. I was able to get the time down to clicks per minute in the query below, but as helpful as that query was, my boos is now asking for the table below.
uid Hit timestamp Previous timestamp (also can use next timestamp) Difference in hours Rolling Status U1 2pm - - - U1 3pm 2pm 1 Might be bot U1 4pm 3pm 1 Might be bot U1 10pm 4pm 6 Not bot U2 8am - - U2 5pm 8am 9 Not bot U2 11pm 5pm 6 Not bot QUERY FOR HITS PER MINUTE
select count(*) as hits, left(convert(varchar, timestamp,121), 16) as minte, uid
from TABLE
where timestamp > '2016-02-01'
and timestamp <= '2016-02-02'
and uid <> '-'
group by uid, left(convert(varchar, timestamp,121), 16) --minute
order by 1 desc
You know this question was answered yesterday in your other thread.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply