This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
They all have unique ids. They all came in through one channel or another (browse, wap, etc).
Now, I want the 'site visit duration' estimate. So I want the max() minus min() timestamp value for each user.
A couple things:
What about multiple visits separated by multipled days or hours? I could group by the user's session_id instead (which I also have in the table), but I'll miss out on their very first hit (because the server is using URL rewriting until the *next* request).
Does the GROUP BY occur *before* the column functions. I'm thinking yes, but I've been bitten by SQL before that 'looks like it works'. Also.. is there a way to get this all in one query, rather than what is above, and then programmaticaly subtracting the two dates? If I try select max(hit_timestamp)-min(hit_timestamp) I get '0' for that entire column.
This is on MySql 3.23.42 [ February 10, 2004: Message edited by: Mike Curwen ]