CODEWITHSUNDEEP
sqlsql-serversubquerymax
Mohammad Naji
Mohammad Naji

Reputation: 5442

Selecting users with top hourly connections

I import IIS Logs into SQL Server.

The table schema:

CREATE TABLE IIS_Log (
    [cdate] [varchar] (255) NULL,
    [ctime] [varchar] (255) NULL,
    [username] [VARCHAR] (16) NULL
    )

Each row in IIS_Log shows a connection between users and the server like this:

username | cdate      | ctime
---------+------------+------
Andy     | 2014-01-01 | 07:10
Andy     | 2014-01-01 | 07:13
Andy     | 2014-01-01 | 07:45
Andy     | 2014-01-01 | 07:05

Andy     | 2014-01-01 | 09:30
Andy     | 2014-01-01 | 09:13

Andy     | 2014-01-01 | 17:45

John     | 2014-01-01 | 12:05
John     | 2014-01-01 | 12:05
John     | 2014-01-01 | 12:05
John     | 2014-01-01 | 12:25
John     | 2014-01-01 | 12:32

John     | 2014-01-01 | 13:00   -- These connections should not
John     | 2014-01-01 | 13:09   -- come in the result set because a heavier
John     | 2014-01-01 | 13:15   -- connection exists for John (with 5 connections)
John     | 2014-01-01 | 13:18   --

John     | 2014-01-01 | 10:10
John     | 2014-01-01 | 10:12

John     | 2014-01-01 | 07:10

David    | 2014-01-01 | 05:20

I want to see hourly total connections of users that have at least 3.

Note that only one row per user is needed. In this example, John shouldn't come twice.

So the example's result set would be this:

username | connections | cdate      | ctime
---------+-------------+------------+------
Andy     | 4           | 2014-01-01 | 07
John     | 5           | 2014-01-01 | 12

Upvotes: 1

Views: 71

Answers (2)

Erran Morad
Erran Morad

Reputation: 4753

My answer is just like dean's. But I don't use CTE. I am also trying to do it without the partition by - 

select *
from 
(
select 
ROW_NUMBER() over (
    partition by username order by username asc, 
    count(username) desc
) as rowid,
username, 
cdate, 
LEFT(ctime,2) as ctime, 
COUNT(username) as cnt
from IIS_Log
group by username, cdate, LEFT(ctime,2)
having COUNT(username) >= 3
) as src 
where rowid = 1

Upvotes: 2

dean
dean

Reputation: 10098

;with x as (
    select username, cdate, left(ctime,2) as hr, count(*) as cnt
    from iis_log
    group by username, cdate, left(ctime,2)
),
y as (
    select *, row_number() over(partition by username, cdate order by cnt desc) as rn
    from x
)
select *
from y
where rn = 1
and cnt >= 3

Upvotes: 3

Related Questions