Three engineers interviewed me and asked the following questions:
For each user find the number of unique visits for each lab.
count(distinct lab_id) number_of_unique_visits
GROUP BY user_id;
count(*) AS number_of_visits
GROUP BY user_id, lab_id;
(SELECT t2.lab_id FROM tst t2 WHERE t2.user_id = t1.user_id GROUP BY t2.user_id, t2.lab_id ORDER BY count(*) DESC LIMIT 1) most_visited_lab
FROM tst t1
GROUP BY t1.user_id;
I usually try not to be judgmental because I only get interviewed to write this blog, but the unfortunate truth is that there are so many jerks in dot-com companies that there are times that it is just impossible to stay imperturbable.
The first interviewer was a typical PHP developer who had no idea about MySQL internals but thought he ruled the world, and could easily optimize any SQL query.
The third interviewer who introduced herself as a chief scientist that does not bother to write any code (yes, she emphasized that) spoke English so badly that it took me 30 minutes to understand her question.
And in the middle of me trying to understand what she was actually trying to say, she simply walked out of the room because she had an urgent call – well, she is the chief genius there.