August 20, 2007 at 7:01 am
I'm not sure how relevant this is, but it kinda fits so here goes.
At a recent interview for a 6-month gig with a financial company, I was asked to solve a number of problems. One of these was to construct (verbally) a query which would return rowcounts and a column total, for each of the seven sets in a three-table join, where each table shared a key - but only for some of the rows. Now, doing this is a cinch for two tables, but I'd not used FULL JOIN in one or two earlier projects, because the requirements better suited a left join with a union with the missing bits. After a few moments one of the interviewers interjected with the solution, which surprised me by including the CROSSJOIN operator.
A couple of days after the interview, (by which time I'd been blown out in favour of a contractor nearing the end of his gig) I was sufficiently puzzled by how a CROSSJOIN could solve this, that I set up a small test and started with FULL JOIN, then figured the critical OR for table 3. Smug and satisfied, because I figured that this was a real-world test, I tracked down one of the interviewers to this forum - he has a very unusual name - and posted the code.
Of course you know what I was thinking - that this would stand me in good stead, if not for this position then maybe the next one - but to my astonishment, the posting hasn't been acknowledged, although it's been read.
Looking at it now, the code is blindingly obvious to me, but nevertheless I can't help thinking that if they're using code to do a similar job, which includes the CROSSJOIN operator, then it's going to be way less efficient then my FULL JOIN equivalent. I can see the guy I posted the code to saying "Hey boss! I've cracked it!" (My code's nothing special - we've all seen what's out there).
So I feel kinda stuffed. Should I have used a "back door" like this? Was my interviewer rude in failing to acknowledge my PM posting? I'd be interested to hear comments.
Cheers
ChrisM
Here's the code: note - no indices for brevity and simplicity in the sample.
Hi Xxxxx
I wasn't sure if the query between three tables used in the interview last wednesday was a real problem or just a sample. In case it's real, here's a solution which avoids cross-joins (as mentioned by Xxxxxxx) which could be expensive.
Cheers
Chris Morris
-- Create sample data: three tables with a common pk and some common rows.
IF OBJECT_ID('tempdb..#Table1') IS NOT NULL
DROP TABLE #Table1
CREATE TABLE #Table1 (pk int, TranVal money)
INSERT INTO #Table1
SELECT 1, 1 UNION ALL -- 1
SELECT 2, 2 UNION ALL -- 1
SELECT 3, 3 UNION ALL -- 1
SELECT 4, 4 UNION ALL -- 1
SELECT 5, 5 UNION ALL -- 1,2
SELECT 6, 6 UNION ALL -- 1,2
SELECT 7, 7 UNION ALL -- 1,2,3
SELECT 8, 8 UNION ALL -- 1,2,3
SELECT 9, 9 UNION ALL -- 1,2,3
SELECT 10, 10 UNION ALL -- 1,2,3
SELECT 11, 11 UNION ALL -- 1,3
SELECT 12, 12 UNION ALL -- 1,3
SELECT 13, 13 UNION ALL -- 1,3
SELECT 14, 14 UNION ALL -- 1,3
SELECT 15, 15 UNION ALL -- 1,3
SELECT 16, 16 UNION ALL -- 1,3
SELECT 17, 17 UNION ALL -- 1,3
SELECT 18, 18 UNION ALL -- 1
SELECT 19, 19 UNION ALL -- 1
SELECT 20, 20 UNION ALL -- 1
SELECT 42, 42 UNION ALL -- 1,3
SELECT 43, 43 UNION ALL -- 1,3
SELECT 44, 44 -- 1,3
IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
DROP TABLE #Table2
CREATE TABLE #Table2 (pk int, TranVal money)
INSERT INTO #Table2
SELECT 5, 5 UNION ALL -- 1,2
SELECT 6, 6 UNION ALL -- 1,2
SELECT 7, 7 UNION ALL -- 1,2,3
SELECT 8, 8 UNION ALL -- 1,2,3
SELECT 9, 9 UNION ALL -- 1,2,3
SELECT 10, 10 UNION ALL -- 1,2,3
SELECT 31, 31 UNION ALL -- 2
SELECT 32, 32 UNION ALL -- 2
SELECT 33, 33 UNION ALL -- 2,3
SELECT 34, 34 UNION ALL -- 2,3
SELECT 35, 35 UNION ALL -- 2,3
SELECT 36, 36 UNION ALL -- 2,3
SELECT 37, 37 UNION ALL -- 2
SELECT 38, 38 UNION ALL -- 2
SELECT 39, 39 -- 2
IF OBJECT_ID('tempdb..#Table3') IS NOT NULL
DROP TABLE #Table3
CREATE TABLE #Table3 (pk int, TranVal money)
INSERT INTO #Table3
SELECT 7, 7 UNION ALL -- 1,2,3
SELECT 8, 8 UNION ALL -- 1,2,3
SELECT 9, 9 UNION ALL -- 1,2,3
SELECT 10, 10 UNION ALL -- 1,2,3
SELECT 11, 11 UNION ALL -- 1,3
SELECT 12, 12 UNION ALL -- 1,3
SELECT 13, 13 UNION ALL -- 1,3
SELECT 14, 14 UNION ALL -- 1,3
SELECT 15, 15 UNION ALL -- 1,3
SELECT 16, 16 UNION ALL -- 1,3
SELECT 17, 17 UNION ALL -- 1,3
SELECT 33, 33 UNION ALL -- 2,3
SELECT 34, 34 UNION ALL -- 2,3
SELECT 35, 35 UNION ALL -- 2,3
SELECT 36, 36 UNION ALL -- 2,3
SELECT 40, 40 UNION ALL -- 3
SELECT 41, 41 UNION ALL -- 3
SELECT 42, 42 UNION ALL -- 1,3
SELECT 43, 43 UNION ALL -- 1,3
SELECT 44, 44 UNION ALL -- 1,3
SELECT 45, 45 -- 3
-- run query SELECT Tables, COUNT(*) AS CommonRows, SUM(TranVal1) AS SUMTranVal1, SUM(TranVal2) AS SUMTranVal2, SUM(TranVal3) AS SUMTranVal3 FROM( SELECT CASE WHEN t1.pk IS NULL THEN '_' ELSE '1' END + CASE WHEN t2.pk IS NULL THEN '_' ELSE '2' END + CASE WHEN t3.pk IS NULL THEN '_' ELSE '3' END AS Tables, t1.pk AS pk1, ISNULL(t1.TranVal, 0) as TranVal1, t2.pk AS pk2, ISNULL(t2.TranVal, 0) AS TranVal2, t3.pk AS pk3, ISNULL(t3.TranVal, 0) as TranVal3 FROM #Table1 t1 FULL OUTER JOIN #Table2 t2 ON t2.pk = t1.pk FULL OUTER JOIN #Table3 t3 ON t3.pk = t1.pk OR t3.pk = t2.pk ) t GROUP BY t.Tables ORDER BY t.Tables
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2007 at 7:20 am
Chris,
I don't think this guy is being rude so much as he's being confused. After all, the interview process is over and someone else has been hired. Depending on what you put in your PM, it could be construed by the interviewer as a very pushy attempt to insist on getting the position.
Think about it from the interviewer's point of view:
When we were hiring at my workplace, we would have the contracting companies test the prospective DBAs with a series of questions that all DBAs should know the answers to. One company, instead of doing it like a test, emailed out the quiz to the contractors and let them fill in the blanks at their own leisure. So of course we got a response where someone copied BOL entries verbatim for the answers to the quiz. This bothered us a great deal. Was this person so incompentant that he/she couldn't even be bothered to at least re-word the BOL entries so the answers sounded like they came from the prospective employee's own mouth?
Needless to say, this person ended up on our rejection list. But in thinking about it, if this person had my email address and tried to send me an email saying "Wait, I know the answers, really I do!" after the rejection, I would be flustered, confounded and not want to send an answer back. After all, that person already had a chance to impress me and failed miserably.
What do they want from me? Are they trying to get back in the running for the job? How does one answer an email like that to begin with? After all, the person sending the email in could take any response the wrong way and start a fight over it, even if the response is polite. And if the interviewer turns around and says "Hey, that's a good solution!", what's to stop you from turning around and using that response in a law suit claiming you were rejected from the job due to prejudiced behavior?
I'm not saying you would sue him, but the interviewer doesn't know that for sure. So, for him, the best response to your PM is no response at all because it keeps him and the company he works for out of legal trouble.
August 20, 2007 at 7:34 am
Brandie
You're absolutely right - I certainly hadn't considered the legal angle.
My post to the interviewer is exactly as above apart from blanking the names. The company has hired several times this year and will probably continue to do so from time to time. My objective was hopefully to get onto / stay on their "possibles" list for future positions, and if not, I've done no harm by providing a solution to what I think is a real problem, not merely a test.
Thanks! Wait a minute...<<failed miserably>> oi! I wasn't that bad you know!
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 20, 2007 at 8:33 am
Chris,
I tend to agree with Brandie. I think you had a good response, but you shouldn't necessarily expect an answer back.
Good luck on the next one!
August 20, 2007 at 5:36 pm
Referring back to me previous post and how I should have been more diplomatic in my response to the recruiter, I think the thing that has been forgotten was that I handled the situation in a business-like manner. I don't get abusive or treat the person badly - I just spoke to them in a manner that would convey that their practices are such that I do not want to be represented by them.
As the city I am in is 1,000km from where the recruitment agency was based and it was not a national chain, I thought that a wake-up call was something that they could use. I've also discovered that many offices of the national-chain recruiters barely talk to each other, so it would have been unlikely that there would have been repercussions anyway.
Besides, we all need an angle when being selected for a position and I've found that many interviewers like the idea of someone who will not be pushed around. This may mean to them that standards will be maintained and the data will remain secure. My impression of someone who won't stand up for something is that they won't maintain the systems and will allow any rubbish practices to evolve because they can be pushed around. It doesn't sit well with the gutless version of political players out there who seek to manipulate and dominate all around them but it does work well with those who understand the meaning of integrity.
A lack of planning on your part does not constitute an emergency on mine.
August 24, 2007 at 9:01 am
Many thanks Steve, and thanks for the feedback Brandie.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 28, 2007 at 8:58 am
Well - I've been on both sides of the fence, so there are arguments on both sides.
I can't know what kind of e-mail you received, but I can profess to how easy it is for the reader to inject/interpret feelings the sender didn't necessarily intend. So - I have had some verbal interviews where I have told the interviewer (nicely, and professionally) that their requirements are way out of line with what they're willing to pay for it, or that the requirements aren't achievable (like - a programmer with 12 years of c# experience, when c# hasn't been in existence anywhere near that long). Of course - I follow that up with a "I'm sorry that it sounds like your expectations and mine aren't going to meet, but if things should change..." but getting the right tone to read through an e-mail alone is really really though.
Nonetheless - any employer that doesn't have the courtesy to keep an interviewee informed should be ashamed of themselves. Of course - it should NOT be personal, but a "the position has been filled" e-mail should be required, as well as whether the resume is to be retained or not. I've received many a call resulting from "sitting in the filing cabinet" after being passed over.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply