"Idiotic" is a strong word. The question is: if candidate A answers these successfully, and candidate B does not, is there a reason to believe that candidate A is a better fit for your SQL data analyst position?

You may say no, that any competent person can read the book and learn this stuff. But in my hiring experience there are sadly oceans of coders who, say, reach for 100 lines of buggy slow Java code to solve something that could be done in 3 lines of SQL. Once you've hired that person they do not magically turn into an efficient or self-aware programmer.

> there are sadly oceans of coders who, say, reach for 100 lines of buggy slow Java code to solve something that could be done in 3 lines of SQL

Such questions don't test for that, they test for rote memorization. I don't learn things by heart I only use rarely, and certainly not to help you with a problem I'm not causing and that such memorization doesn't fix.

First of all, in your example, the DB server is doing the other 97 lines of work that java had to do; one approach uses data structures the other uses a SDE designed for data management and access. I think most employers would prefer a developer that can do both.

Second, the most important skill to DBA or Data Scientist role and to an employer is finding someone that understands the data domain well enough to point out obvious mistakes, bad approaches and thinking to managers and provide leadership, not just access, to information.

Third, "any competent person" is just belittling a skillset that, like programming, can take years or decades to truly understand.

I agree with most of what you are saying. But sometimes it's better to go with Java code instead of creating 3 line SQL commands.

Then again, this depends a lot on your architecture. For instance, if your database is running on expensive hardware with very limited CPU and memory quotas then often it's better to export intensive problems to the application. Also a lot of the more complicated SQL commands are implemented differently in all the dialects of MySQL, MSSQL, Oracle and DB2. Sticking to simpler queries lends itself to cross platform compatibility if you need that.

I think that when you run into someone who knows these in depth things about SQL you either have a smart technically aware person or a rote learner. I think the best bet is to ask them how they would use the different stuff and why. Rote learners will hit a brick wall or answer generically.

These examples are not obscure at all. If the job involves working with a lot of complex SQL, then these are some great questions to test the candidate's deep understanding of relational queries. It will be critical in their ability to maintain and write SQL quickly. Time and accuracy is of the essence in many such roles. However, the "if" is very important and often ignored. A web developer doesn't need to be interviewed for complex SQL.

That’s because evaluating talent is really hard, and this is the best proxy we have. The dirty truth is that promotion’s is usually even more arbitrary/political. I’ve seen extremely talented people be denied deserved promotions multiple times and people so bad I have no idea why they have a job get promotions. It’s more about who you know than what you bring to the table.

It isn't the best proxy though, because it's not even trying to simulate the conditions under which real work is done (e.g. in real work, most developers will often look up details, and it's no problem at all - what matters is how they think about solving problems, that they know what they don't know, and that they fit into the team). This is a proxy for 'is this an experienced person with a strong memory?', not 'is this a qualified candidate who would perform well in the position?'. It's quite possible to fit the latter and not the former. If you're the size of e.g. Google so your problem is more to weed out bad candidates quickly rather than catch all good candidates, then sure, go ahead (false negatives are acceptable to completely avoid false positives). If you are a smaller company, then your pool of applicants is quite limited and it's much more important that you don't pass over any qualified candidates that happen to fall into your net.

Why do you assume candidates can't look information up during interviews? Karat is probably the largest provider of technical interview as a service and they allow candidates to look things up while they're interviewing. The goal is to learn how the candidate works and what they can accomplish, not simply what they've memorized.

I interviewed quite a few times when I was at google, and in very few cases would I deduct score for something that could be looked up online. I always started the interview with saying that if they wanted to look something up they could ask me, and if I didn’t know we could make it up and pretend the API existed. I frequently discussed this with others and they all did the same.

The point is absolutely not to find some technicality and use it against the candidate.

While I agree that testing someone on concepts is more favorable than testing someone on tool specifics, I absolutely think there's merit to (tool) proficiency.

If there's one thing I've noticed among the "10x" coders, then it is that they know their environments and tools like their back pockets.

This isn't rote memorization at all. They're typical real-world problems to solve, and the point is to ensure the candidate can "think their way through" the problem to come up with a solution: not to come up with a solution using a specific function or keyword.

A good interview would allow you to read SQL documentation to solve this. If you knew it off the top of your head, you'd ace the interview.

If you can't solve the problems after looking it up, then perhaps you are not a SQL expert. I think these sort of questions are valuable.

I can't blame someone for wanting to filter for SQL expertise. I don't know if these questions are a good way to do it. Maybe they'd be good bonus questions for when you're trying to different between two good enough applicants.

Do you have suggestions on how to get real with this?