On Fri, 07 Mar 2008 16:58:58 +0000, Orchid XP v7 <voi### [at] devnull> wrote:

>The final one was quite an interesting problem though... I'm wondering 
>if anybody has a solution.
>
>Given a table containing all the page hits, count how many visits the 
>website has had. (Two hits are the same "visit" if they're the same 
>username and happen within 10 minutes of each other.)

In Oracle, assuming userid is the user and visittime is the timestamp, I'd do it like
this ...

<SQL>

select 
   userid,
   sum(case when (visittime-priortime) > 10/1440 then 1 end) visits
from (
   select 
      userid, 
      visittime,
      nvl(lag(visittime) over (partition by userid order by visittime), visittime-1)
priortime 
   from visittest
   )
group by userid;

</SQL>

I can tell you for sure that I wouldn't have been able to pull that our of my butt on
the fly while testing.  I would have had an idea of how to do it, but would't recall
the syntax.


>PPPS. http://www.xkcd.com/138/


heh.

Post a reply to this message