They’ve got SQL smarts and the certification to prove it. But do they have what it takes to be the next Microsoft employee?Watch the challenges unfold on our website beginning July 24, 2012: http://aka.ms/bethenext
Wow, this “Be The Next Microsoft Employee” thing is the real deal! Real contestants, nervous contestants, eager contestants, this should be interesting! Turns out at least this week’s was :-). As Buck mentioned in his earlier post, the challenge we gave the contestants can seem simple at first: “Find all products provided by more than one vendor.” + “For extra credit, give us the number of vendors for each of these products”.
As it turns out and just like in real life, things like time, documentation and someone to ask questions were scarce.
The hour available to solve the challenge passed in… Well, one hour. As we were waiting for the contestants to present their solutions I was very curious—what would they come up with? For this solution the natural answer is:SELECT ProductID ,COUNT(*) AS VendorCountFROM Purchasing.ProductVendorGROUP BY ProductIdHAVING COUNT(*) > 1;
Wait, that isn’t what Buck said the answer was! That is the beauty of programming, there is rarely just one right answer (also, no extra credit answer in Buck’s post). Buck’s answer is:SELECT DISTINCT pv1.ProductIDFROM Purchasing.ProductVendor pv1INNER JOIN Purchasing.ProductVendor pv2ON pv1.ProductID = pv2.ProductIDAND pv1.VendorID <> pv2.VendorID;
Comparing these two solutions, the first one is the simpler and also more efficient one. However, being a mischievous fellow, Buck really likes the above solution as it is available in our documentation (yes, including ProductVendor etc.).
At any rate, as the contestants start showing their solutions I get a glimpse into the stress the competition adds. For example, Alex, who finished first (half way through the hour or so), had several issues with his solution. For starters he didn’t query the database he had restored from the USB stick. He also complicated the solution by including more tables than necessary in his query.
Chris on the other hand was really close, a simple nudge from my side during his presentation gave him the right answer. Chris spent far too much time making sure his T-SQL looked great, wrapping it in a stored procedure etc. This could have been good for additional credit if he actually got the right answer.
Stacy could explain the problem and logically what she wanted to accomplish, but didn’t get all the way there.
Mike was right on the money, he came in, gave us the answer up-front and crisply explained what he did & why. He was also the only one that eluded to double checking that the data he had loaded at least wasn’t corrupt. Mike did this by trying out the query against the AdventureWorks database he had installed prior to the challenge and, surprise, surprise, it seemed to contain very similar data :-). Just like I had asked the other contestants, I asked Mike if there was anything he was worried about. I liked that he thought about it and said that he couldn’t come up with an answer but got worried by my question. What I was looking for was if the contestants did at least some sanity checking of the data. Mike did this, but his check wasn’t really the right one in my opinion as having the exact same table with the same data available probably isn’t the common case. I was looking for a check whether or not there were duplicates on Product and Vendor combinations (through checking for a constraint or using a separate query). If there were, then the counts would obviously be off and your trust of the data would most likely go down…
In conclusion, what I looked for was:
I feel Mike showed all of these characteristics and being the only contestant with the right answer he was the clear winner of this challenge. Congratulations, Mike!!
Mike did really the right crucial thing. however an amazing team :)