"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

Gail Shaw wrote:

>> 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

Yeah, well, like I said, the test was pretty basic. I was expecting them 
to test me on something, like, hard...

-- 
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:47d26114$1@news.povray.org...
> Gail Shaw wrote:
>
> >> 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
>
> Yeah, well, like I said, the test was pretty basic. I was expecting them
> to test me on something, like, hard...

That last question wasn't exactly easy.

Still, a lot of the people I get to interview wouldn't do well on that test.

Post a reply to this message

>> Yeah, well, like I said, the test was pretty basic. I was expecting them
>> to test me on something, like, hard...
> 
> That last question wasn't exactly easy.
> 
> Still, a lot of the people I get to interview wouldn't do well on that test.

As I said, most of the questions were like "list all the posts that have 
more than 10 posts, sorted in date order". Really taxing...

[In case it isn't clear, you'd need to perform a join for that. But even 
so...]

-- 
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:47d27e74@news.povray.org...
> >> Yeah, well, like I said, the test was pretty basic. I was expecting
them
> >> to test me on something, like, hard...
> >
> > That last question wasn't exactly easy.
> >
> > Still, a lot of the people I get to interview wouldn't do well on that
test.
>
> As I said, most of the questions were like "list all the posts that have
> more than 10 posts, sorted in date order". Really taxing...

And as I said, a large number of people I've interviewed would have done
really badly on that.

Do I have to repeat myself any more times? I did comprehend what you wrote
first time.

Post a reply to this message