AndyHopper
Programmer
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!
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!