Orchid XP v7 wrote:
> just don't remember the command syntax off the top of my head. 

Maybe that's because different engines use different syntax. :-)

> Another 
> wanted me to use "cursors" - something I've never ever done before. I 
> don't even know what one *is*.

It's kind of like imperative Haskell - try to avoid it. ;-)

> I sat for ages puzzling over this one. I can't figure out how the heck 
> to do it... any pointers?

I would first find the duration of each page view, then look for page 
views more than 10 minutes apart.

The duration is the difference between the view time of this row and the 
min of the view time of all rows with a later view time (select by user 
name or whatever your criteria are).  Unobvious. I had to ask that one 
myself.

After that, find the distinct user/duration>=10 pairs.

-- 
   Darren New / San Diego, CA, USA (PST)
     "That's pretty. Where's that?"
          "It's the Age of Channelwood."
     "We should go there on vacation some time."

Post a reply to this message

Nicolas Alvarez wrote:

> Remember that "unskilled and unaware of it" paper? The more you know, 
> the more you know you don't know too (does that make any sense?). People 
> may call themselves experts and not know what a JOIN is, and you say 
> you're somewhat rusty because you know about lots of features that exist 
> but you don't know very well, when you're actually much better than the 
> idiot calling himself an expert.
> 
> Does that make any sense either? Seems it's impossible to explain this 
> without long tricky sentences :)

Hmm... it has a flavour...

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message

Darren New wrote:
> I would first find the duration of each page view, then look for page 
> views more than 10 minutes apart.

Or, to clarify. Count the number of sessions that *ended*.  Much easier 
than counting the number of sessions that started.  Finding the end of a 
session is finding the row where there's no row with a greater timestamp 
but within 10 minutes of this timestamp.

-- 
   Darren New / San Diego, CA, USA (PST)
     "That's pretty. Where's that?"
          "It's the Age of Channelwood."
     "We should go there on vacation some time."

Post a reply to this message

Darren New wrote:
> Darren New wrote:
>> I would first find the duration of each page view, then look for page 
>> views more than 10 minutes apart.
> 
> Or, to clarify. Count the number of sessions that *ended*.  Much easier 
> than counting the number of sessions that started.  Finding the end of a 
> session is finding the row where there's no row with a greater timestamp 
> but within 10 minutes of this timestamp.

Care to write an SQL statement? ;-)

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message

"Orchid XP v7" <voi### [at] devnull> wrote in message
news:47d17435$1@news.povray.org...


> The last three completely stumped me. One wanted me to take some data
> from one table and insert it into another one. I know how that's done, I
> just don't remember the command syntax off the top of my head.

INSERT INTO SomeTable (<Column names>)
SELECT <Columns> FROM SomeOtherTable
WHERE <Any relevant conditions>

> Another
> wanted me to use "cursors" - something I've never ever done before. I
> don't even know what one *is*.

Slow and inefficient.

> PS. Apparently "I see plenty of people who claim to know SQL who do
> worse than you did. You're really underselling yourself, Andrew. I
> wasn't even expecting you to ATTEMPT those last few..."

Yup. I see that too. People claiming to have 4 years of SQL experience and
can't write a  basic select statement

Post a reply to this message

"Orchid XP v7" <voi### [at] devnull> wrote in message
news:47d19c72$1@news.povray.org...
> Darren New wrote:
> > Or, to clarify. Count the number of sessions that *ended*.  Much easier
> > than counting the number of sessions that started.  Finding the end of a
> > session is finding the row where there's no row with a greater timestamp
> > but within 10 minutes of this timestamp.
>
> Care to write an SQL statement? ;-)

Very rough and probably wrong. It's late and I'm not going to test this

SELECT Stuff FROM someTable a
WHERE not exists
 (SELECT 1 FROM SomeTable b where DATEDIFF ('mi',a.timestamp,b.timestamp)
between 0 and 10 AND a.UserID = b.UserID and a.HitID != b.HitID)

That should give all hits that don't have another hit with the same user ID
in the next 10 minutes.

It's not particuarly efficient, but I don't care right now.

Post a reply to this message

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

On Fri, 07 Mar 2008 18:04:53 +0000, Orchid XP v7 wrote:

> Nicolas Alvarez wrote:
> 
>> Andrew, I think your warning not to give "that kind of pointers" has
>> backfired badly.
> 
> ...apparently. *rolls eyes*

I think you just rolled "snake eyes"!  (I'm seeing two eyes with one pip 
each)

Jim

Post a reply to this message

> If you give a person a NULL pointer, does that kill them?



Only in those "fatal exceptions" :)

Post a reply to this message

>> If you give a person a NULL pointer, does that kill them?
> 
> Only in those "fatal exceptions" :)

GAAAAH! >_<

Please stop... :-{

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message