Complex grouping to analyze web stats

[AndyHopper]

Each time a page is viewed on my website, the time, session, url, and refferer are recorded. I'm trying to analyze this information to make some sense out of it. I've managed to accomplish almost everything I'm looking to do except for the following tasks.

This is the table structure:
Timestamp
SessionID
RequestedURL
Refferer

I'd like to determine the following information:

Top Exit Pages
This is what I need to do:
1.) group rows by SessionID
2.) order each group of rows by Timestamp DESC
3.) select top 1 * from each group of rows
4.) group the remaining rows by RequestedURL and count the number of rows which contains each RequestedURL

Average Visitor Duration
1.) group the rows by session id
2.) find the differences between the oldest and newest Timestamps in each group
3.) average the differences

Average Number of Pages Per Visit
1.) group the rows based on the sessionID
2.) count the number of rows in each group
3.) average the counts from step 2

I'm wondering if/how I can accomplish the above tasks with a single TSQL statement. Any help will be much appreciated. Thanks!

 

[rt63]

Try these. I have some doubts whether I have understood your requirements for top exit pages.

1. top 1 * from each group

select a.*
from your_table a ,
(select SessionID , max(Timestamp) as last_tm
from your_table
group by SessionID) t1
where a.SessionID = t1.SessionID
and a.Timestamp = t1.Timestamp

2.remaining RequestedURL and their counts

select RequestedURL , count(a.RequestedURL)
from your_table a ,
(select SessionID , max(Timestamp) as last_tm
from your_table
group by SessionID) t1
where a.SessionID = t1.SessionID
and a.Timestamp <> t1.Timestamp
group by a.RequestedURL


3.Average Visitor Duration (in minutes)

select avg(datediff(mi,last_tm,first_tm))
from
(select SessionID , min(Timestamp) as first_tm , max(Timestamp) as last_tm
from your_table
group by SessionID) t1

4.Average Number of Pages Per Visit

select avg(pages)
from
(select SessionID , count(*) as pages
from your_table
group by SessionID) t1

RT

 

[AndyHopper]

Thanks RT!!! I didn't realize subqueries can be selected from like that. Here are the statements which I'm now using to accomplish exactly what I described above .

Top Exit Pages
Select Count(RequestUri) as hits,ServerName,RequestUri FROM Web_Log b WHERE LogID IN( select a.LogID from WEB_Log a , (select Cookie , max([Timestamp]) as last_tm from WEB_Log WHERE AccountID=1 group by Cookie) t1 where a.Cookie = t1.Cookie and a.Timestamp = t1.last_tm) group by RequestUri,ServerName

Average Number of Pages per visit
select avg(pages) as avgPages from (select Cookie , count(*) as pages from WEB_Log WHERE AccountID=1 group by Cookie) t1

Average Visit Length
select avg(datediff(mi,first_tm,last_tm)) as AvgVisitLength from (select Cookie , min(Timestamp) as first_tm , max(Timestamp) as last_tm from WEB_Log WHERE AccountID=1 group by Cookie ) t1