Job Application Test from Hell

[KP Lee]

This doesn't look like a question geared to testing your SQL skills, but your skill at defining "real" requirements from something that seems very silly at first. 1. Assume the person asking this, isn't a lune escaped from the asylum. 2. Try to find the underlying order that they are requesting. 3. ASK them what the underlying order is, suggesting a possibility. 4 ASK them the schema information you need in order to write the query. Sort of like: I see these regions are all in South Africa. I'm not that informed about that area, are you ordering this query by population? No? What is the order criteria you are using? Then find out if the criteria is in the table you are querying? Or they could be testing your knowledge of DB design and waiting for you to ask why the H the region and the name are stored in the same table in the first place. Failing all that, just answer the question. There are a bunch of ways to do it. You can throw in a case statement in a batch select and select the two fields and order by the case result, the union all solution would work, creating a temp table and joining with it is a third option.

[James H]

SELECT Region, Contact FROM RegionTable ORDER BY SubString(Region,2,1) DESC, Contact ASC

[James H]

Ha - you beat me

[Mark_Wallace]

It's a ridiculous question. If there's a reason for ordering as in ex.2, then there must be data that allows the order in that or another table (area code, telephone dialing code, or whatever). If such data isn't available, then the correct answer to the question would be to add a column for it. Otherwise, if it's just an arbitrary order for existing data, use an arbitrary solution -- the second letter of each location in reverse alphabetical order, for example -- then stuff it up the questioner's @rse.

I wanna be a eunuchs developer! Pass me a bread knife!

[PhilLeTaxi]

Hi, With this request,

SELECT region, contact FROM `localisation` ORDER BY SUBSTRING(region,2,2) DESC, contact ASC

I obtain :

region contact
Durban John
Durban Mary
Johannesburg Frank
Cape Town Anna
Cape Town Fred
CapeTown Joe

The list is ordered regarding the second letter of the region. To avoid the missing blank in CapeTown, the end of the string is cut. Bye

[Brady Kelly]

It didn't strike me as that It's in order of Region, Contact asc, but it starts at the 2nd region, wrapping around to the first.

[Xapp]

SELECT * FROM t ORDER BY SUBSTRING(REPLACE(Region, ' ', '') FROM 2) DESC, Contact ASC Tricky.

[PhilLeTaxi]

Hi Again, Oups, I didn't see the response of James H. which has found the same solution. Sorry for the noise. Bye

[Narud Shiro]

Guessing that we are using talking of SQL Server, and without using case when, union, or any other thing like them, this is my best: select Region, Contact from Contacts order by replace(Region, ' ', ''), Contact Can you give me a B+ at least, teacher?

[PhilLeTaxi]

Hi, Your request gives this result :

region contact
Cape Town Anna
Cape Town Fred
CapeTown Joe
Durban John
Durban Mary
Johannesburg Frank

This is slightly different from the expected result. Bye

[ghle]

Christian Graus wrote:

OK, so it's sorted by two fields we can't see ( region and email address ) ?

I think he meant that the test was sent to him via e-mail, not that e-mail was a hidden field.:confused: Heck, I'd just order by the hidden Sequence field. :)

Gary

[Member 96]

A job application test that doesn't reflect a real world problem is an utter waste of time and whoever devised this should be kicked in the balls and repeatedly told to "get real".

---

“If you want to build a ship, don't drum up people together to collect wood and don't assign them tasks and work, but rather teach them to long for the endless immensity of the sea” - Antoine de Saint-Exupery

[Kirk Wood]

SELECT 'look for different job' FROM 'places not full of self serving jerks who wish to prove their supposed superiority' Personally, my experience is that places that serve up such a test are full of jerks who think too highly of themselves. They find great pride in their ability to find questions few can answer, and probably can't produce anything worth having anyway. The number of people who think they are great far exceeds the number who really are great.

[Brady Kelly]

Well, the one that did reflect a real world problem, probably the only one, was: Given a list of names, identify duplicate names as well as possible misspellings of the same name. I scrawled something about soundex in my answer. This was a written test taken after hours. I had nobody to raise issues with, but plenty of time to really think things through.

[TraceyTiethoff]

That's a good point Christian. My answer would be in the form of a question asked with the general idea of "What exactly are you trying to accomplish?". I can't imagine a reason for this sort order, as someone else mentioned. Either the order is insignificant, the significance is merely that some user likes it, or the data provided isn't complete and therefore doesn't reveal any logical reason for this order. I know programmers are often stuck with "just do it", but if the customer is open to it, I would prefer to have a discussion of the cost(both present and future) versus the benefit of having it this way (assuming it isn't an incomplete data issue).

[ZRonZ28]

Fig. 2 Region Contact Durban John Durban Mary Johannesburg Frank Cape Town Anna CapeTown Fred Cape Town Joe The pattern seems pretty simple: 1. Region is reverse alphabetized using the second letter of the word (or maybe 2,3 and 4?), 2. Contact is alphabetized within the Region.

modified on Friday, November 26, 2010 10:50 AM

[dada2010]

Haha, i think i found the hidden order... Region are simply ordered by descending their longitude, contacts by the length of their name (descending) then by their fourth letter (ascending). As we know commonly longitudes of theses cities/regions are Cape Town : 18.45° Durban : 30.6° Johannesburg : 27° For the understanding, we suppose we have a table containing longitudes (while in real life we would need -of course- the request to connect to googlemap api to find it) (googlemap find CapeTown and Cape Town same longitude): wtf_region_longitude region longitude Cape Town 18 CapeTown 18 Durban 31 Johannesburg 27 and the table of fig 1: wtf_region_contacts Region Contact Cape Town Fred Cape Town Joe Cape Town Anna Durban John Durban Mary Johannesburg Frank Doing permutation between Joe and Fred, the request is :

select s.region, contact from
(
select c.region,
contact = case contact when 'Joe' then 'Fred' when 'Fred' then 'Joe' else contact end
,contact as ocontact
from wtf_region_contacts c
) s, wtf_region_longitude l
where s.region=l.region
order by longitude desc, LEN(contact) desc, SUBSTRING(contact,4,1) asc

--> region contact Durban John Durban Mary Johannesburg Frank Cape Town Anna CapeTown Fred Cape Town Joe Am I right ?

modified on Friday, November 26, 2010 4:30 AM

[Brady Kelly]

I think your scenario is actually more likely than the real pattern being sorting by the second char of region.

[CodeNaked]

Durban and Johannesburg are both divisible by 6.

SELECT * FROM Regions
ORDER BY
CASE LEN(Region) % 6
WHEN 0 THEN 0
ELSE ASCII(SUBSTRING(Contact,1,1)) END,
Region, Contact

Darwin

[narfnarf13206]

--There is no obvious sort in fig 2l; we have no specific req saying to order using a particular approach...so brute force order it. Obviously this solution wouldn't scale..then again any solution that makes an assumption that there is a scalable way to order it would only be correct by pure luck. Note - pseudocode, might have syntax errors but the basic methodology works select * from fig1 a into #temp01 --dump data into a temp table alter table #temp01 add orderr integer --add a new column update #temp01 set orderr = 1 where region = 'Durban' and Contact = 'John' --populate order info update #temp01 set orderr = 2 where region = 'Durban' and Contact = 'Mary' --populate order info --TODO update orderr for 4 remaining rows select region, contact from #temp01 order by orderr