[oarabbus_]

> I do think there's some signal in whether a candidate can get the syntax right.

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.

[tempguy9999]

> > I do think there's some signal in whether a candidate can get the syntax right.

> 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!)

[oarabbus_]

>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)

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`".

[tempguy9999]

First up let me apologise for the abrasive and somewhat unpleasant reply I gave to you. Not my best, sorry.

Ok, couldn't find a sample on the web so here's mine. It's not right for brevity and because this is the first python code I've done in ~3 years, so any criticisms welcome. Hopefully can get the formatting right

  # super.init omitted for brevity
  class Expession: # abstract base class
      def eval(): pass

  class Literal(Expession):
      def __init__(self, val): self.value = val        
      def eval(self): return self.value

  lit1 = Literal(8)
  print(lit1.eval()) # prints 8

  class UnaryExpr(Expession): pass # base class for unary expressions

  class Negate(UnaryExpr):
      def __init__(self, expr): self.expression = expr
      def eval(self): return - self.expression.eval()

  lit2 = Literal(13)
  neg = Negate(lit2)
  print(neg.eval()) # prints -13

  class BinaryExpression: pass # base class for binary expressions

  # Note that subclasses Add and Multiply have the same
  # __init__ code so I should hoist that into the BinaryExpression
  # base class but for clarity I'm leaving it in the subclasses

  class Add(BinaryExpression):
      def __init__(self, leftExpr, rightExpr):
          self.leftExpression = leftExpr
          self.rightExpression = rightExpr
      def eval(self):
          return self.leftExpression.eval() + self.rightExpression.eval()

  add2literals = Add(lit1, lit2) # 8 + 13
  print(add2literals.eval()) # prints 21

  class Multiply(BinaryExpression):
      def __init__(self, leftExpr, rightExpr):
          self.leftExpression = leftExpr
          self.rightExpression = rightExpr
      def eval(self): return self.leftExpression.eval() * self.rightExpression.eval()

  mult2literals = Multiply(lit1, lit2) # 8 * 13
  print(mult2literals.eval()) # prints 104

  # now let's make a complex expression, say (7 + 2) * (-4)
  # Doing this by hand but a parser would build this from that
  # string
  expr = Multiply(
      Add(Literal(7), Literal(2))
      ,
      Negate(Literal(4)))
  print(expr.eval()) # prints -36

Basically it's a tree of objects that you call eval() on the root, and these recursively call eval down, then when they reach the bottom start returning their subtree-calculated values.

Make sense?

Re. the left join, I abbreviated it. Full event was that there was 2 interviewers, me + other guy. I said to our interviewee, "what's a left join?". Cue puzzled expression and headshake. My co-interviewer qualified that for him: "what's a left outer join?", getting the response "never heard of it". He claimed 4 years of sql on his CV. No job for you, matey.

This isn't rare either, worked at a recruitment office and overheard a conversation which recruitment agent used to check applicant wasn't clueless. Applicant was applying for C++ job. Q: "give me 4 STL containers". Applicant replied "cin and cout".

If you've done no C++ that's like asking a python guy "give me some python data structures" and getting back the reply "input() and print()"

Edit: to clarify about the expression eval stuff, I wasn't expecting code, just an obvious grasp of a tree of objects with relevant subtypes, and eval(). He knew roughly how to do it procedurally, but blatantly had no clue on the OO style (which, yes, he claimed to have on his CV).

Incidentally, I'm just starting my very first step into Pandas today. Looks SQL-ish!

[jefftk]

Again, I don't think this is the only signal, or the most important signal, but there's still signal there. Imagine I get two data science candidates who seem basically the same and say they work in SQL daily. One of them can comfortably write out a JOIN with a GROUP BY and an ORDER BY when the problem calls for it, the other struggles and makes a lot of errors. I'm going to guess that the former is better at SQL, acknowledging that this is an imperfect proxy.

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.