The main things I want to see, though, are: can you communicate well about the parts of the problem that aren't clear to you? Can you analyze and compare solutions? Can you figure out something reasonably efficient? Do you understand your solution well enough to code it?
(Speaking for myself, not my employer.)
I know you're talking about software engineers, not data analysts, but:
select from <table> [oops... you're supposed to put an asterisk there]
select <column>, count(field) from <table> [holy shit... I forgot the group by]
select <column>, case when <condition> then <result> else <other> from <table> [oh man, case statements need to be terminated with an `END`]
select <columns>, from <table> [oh no... SQL doesn't like that comma before the from statement]
select <...> from <table1> join <table2> table1.column = table2.column [This is embarrassing, I forgot the `on` keyword]
select <stuff> from <table1> union <stuff> from <table2> [Jesus... I forgot to type `select` after the union]
select <column>, sum(column2) over (partition by column3 between unbounded preceding and current row) as cumulative_sum from <table> [dang, SQL doesn't know which rows if I don't actually mention `ROWS BETWEEN`]
select <column>, count(case when <condition> then 1 else null end) as count from <table> order by count desc having count > 1 [ahh that's silly, you can't put your HAVING clause after the ORDER BY]
with cte_1 as (select <...>), cte_2 as (select <...>) cte_3 as (select <...>) select <columns and aggregations> from cte_1 join cte_2 on <...> left join cte_3 on <...> where <condition> group by <many columns> [Oh my goodness, I forgot a comma after closing cte_2]
Now, perhaps this says more about myself more than anything, but I really do write code comfortably all day, I'm glad my current employer, or any number of the clients I've worked for, haven't had this philosophy (even when watching over my shoulder waiting for results they need at the moment). I'd be mortified if anyone ever dug up some of the atrocious things I've requested of the database in the server logs.
> I know you're talking about software engineers, not data analysts, but:
(inserts self-pwn car crash here)
I've done SQL for ~20 years and I'd say I'm good at it. I don't make as many mistakes as you've described but I know exactly what you mean, and I'd never hold that against you because I don't give a toss about mistakes that the language will catch.
I've rarely interviewed others, but when I did I asked high-level stuff approaches. I wanted to see if they could grasp the solution, not the physical framework.
Actual example: you're given a large collection of words, which you're allowed to pre-process - you have plenty of time to do this. Later on you are given another word, how would you very quickly find all acronymns of that word?
(inerviewee programmer didn't get it, so I tried it on a non-programmer we had around - she very quickly worked out you ordered the letters and saved them - she didn't explain it clearly (to repeat, she wasn't a programmer) but in programmer terms it was a dictionary with keys as the sorted letters and the values as a set of the words).
In this case, would you employ the supposed programmer who didn't get it, or the non-programmer who did?
Actual example: Show me how you'd represent an arithmetic expression using objects, and how you'd evaluate it in an OO style (was after class hierarchy of (op, leftexpr, rightexpr and .eval method. With plenty of time and pushes in the right direction, he still didn't get it despite claiming good OO on his CV)
(True story: to same guy who didn't get the OO expression question, I started off with an SQL question. His CV said SQL was his strong point, so I gave him an easy one: "explain to me what a left outer join does". He shook his head in confusion "Never heard of it". Actually happened! I'm not even exaggerating!)
As a SQL guy who knows Python, but specifically just pandas/seaborn/numpy (matrix/set operations rather than the underlying constructs which make numpy/pandas possible), as opposed to a SWE with OO skills, could you point me in the right direction to learn how this question should be answered?
>"explain to me what a left outer join does". He shook his head in confusion "Never heard of it". Actually happened! I'm not even exaggerating!
I... I don't even know what to say here. That's absurd to me he would claim SQL knowledge and respond with that answer.
My response would be "that is the same as a `left join`" (then I'd explain what a left join was) and follow up with "I exclusively write 'left join' and never 'left outer join' as my experiences with the DB/MS I'm most familiar with (Postgres, Redshift, MySQL, MSSQL and a couple others) accept the `left join` syntax without specifying `outer`".
I'm also not nearly as picky as a compiler/interpreter. I can tell what you're trying to write, and lots of people make silly mistakes. But a high volume of mistakes, or especially difficulty getting anything substantial out at all, those start to make me worry.
[#] If this doesn't seem obvious consider the inverse - worse designs will inevitably require more lines of code to get the same result.
I think that's extremely dependent on context.
I write mostly SQL and bash and would argue that both languages are not conductive to try to reduce size at the cost of future maintenance.
I agree with that. I've had candidates explain certain points of syntax as they worked, or demonstrate their knowledge in other ways, or write their code particularly well or cleanly, and I make sure to mention that positively in my notes. But less-than-flawless syntax by itself isn't a negative for me.
I agree with the main things you look for - I too try to focus on those more than syntax.
