5 ms·

My go to interview question to test basic SQL knowledge is use of “HAVING”. It’s a surprisingly good filter. EDIT: To clarify since this got more attention th

My go to interview question to test basic SQL knowledge is use of “HAVING”. It’s a surprisingly good filter. EDIT: To clarify since this got more attention than I expected...I don’t disqualify any candidate based on the answer to a single question. I just use that question to assess their actual SQL experience. If you’ve spent any amount time writing raw queries by hand for reports or just to pass through to a web service, you’re going to have run into HAVING. It’s a simple part of the basic SELECT syntax without needing to involve joins, subqueries, index optimizations or specific knowledge of database internals. It’s a fair question and the only way you wouldn’t have run into it is from working purely with ORMs or NoSQL. There’s a lot you can do without it, but it goes a long way in determining my ability to ask you to open up a query analyzer in different environments. It’s also going to tell me a lot about the way you think about data problems and where you are going to gravitate for certain types of logic.

sitkack 7y ago
I like to structure my questions such that they could using HAVING or a CTE or subquery and not test on the piece of knowledge directly, but the application of knowledge. Like in Python if you asked lots of questions with list of numbers ... and then generalized it to infinite lists of numbers. If the first case I'd like to see list comprehensions, and then generator expressions or possibly the use of itertools.
- brightball 7y ago
  That tends to be how it leads in. We’ll talk through building a simple CRUD application with a single table and then ask questions about how to get specific data for somebody in marketing. There are a lot of ways to answer it, but HAVING is the simplest.
rockinghigh 7y ago
How would you know it’s a good filter? What position are you interviewing for?
- kradroy 7y ago
  It's about as good a filter as asking a candidate what a "gyascutus" is.
  - FpUser 7y ago
    ROFL. You've made my day.
snarfy 7y ago
I can barely get candidates to join two tables.
- deleted 7y ago
  [deleted]
FpUser 7y ago
My use of SQL beyond very basic is very sporadic (I do remember about HAVING though). If you ask me out of blue I would most likely fail tests. From time to time however I do have to design database for particular product and in a matter of few days my knowledge gets from basic to very good one. Only to go down again a short while after database design implementation and testing completes. That does not mean that I do not know basic concepts about how different databases work, including underlying storage engines etc. To the point that for one product I personally designed actual NoSQL (EAV to be precise) database engine and query engine largely resembling SQL to go along. So would you fail me in your test?
- freedomben 7y ago
  Exactly. You don't use it, you lose it. That doesn't mean you don't have the capacity to reacquire it in a short period should you need it again. Most humans are not encyclopedias. I can describe for you in detail the different index types on MySQL and Postgres, and go into when/why you would use each. But it's been so long since I crafted a complex join by hand I would definitely have to google it. However I don't doubt that I could write the query in a short time. One of the most realistic interviews I ever had, they gave me a laptop and allowed me to google during my answer. I took the job.
kradroy 7y ago
Eh, HAVING is something that can be learned in 5 minutes. So filtering out for that is probably generating a lot of false negatives. I quiz them on indexes and table design. Those can be done without writing a single SQL query.
- SkyPuncher 7y ago
  I look up HAVING every time I write it. I just don't use it enough to keep it in memory as a first class concept. A quick google search with an example is enough to jog my memory. If I got asked about it on an interview, I'd be completely blank.
  - babuskov 7y ago
    I'm curious now. What's there to look up? It's just like WHERE, but you use it on aggregate values. WHERE filters the data before you aggregate it, HAVING afterwards. That's all there is to it.
    - alasdair_ 7y ago
      Simply remembering that fact is language trivia that is easy to refresh when needed. It’s like asking if intel x86 is big-endian or little-endian. It’s trivial to check but if I haven’t had to use that fact in a while, I likely won’t bother to remember because it’s trivial to search for.
  - ordinaryperson 7y ago
    Exactly. "I use [x] all the time and know it well, so [x] will be a good filter for evaluating someone's technical skills." No. People do different work tasks depending on the project or company. Sometimes I'm knee-deep in SQL. Others in React. I'm often forced to do PHP. My familiarity with specific SQL syntax ebbs & flows with what I'm doing. There's no point in keeping something in your brain if you're not going to use it every day. Just remember the general concept and Google the specifics when you need it. Just one example why interviewing is terrible and interviewers really should be trained on what not to do.
dadoge 7y ago
Is the pun of saying knowing “HAVING” is a good filter intended?
- brightball 7y ago
  It is now.
scarejunba 7y ago
My experience with interviewers is that they all believe that their pet question is "a surprisingly good filter". Considering that there is rarely a data-driven approach to recruiting, usually this statement has a heavy confirmation bias. Usually when you add data to the mix, you'll find that most questions are not significantly correlated with candidate success/failure once you condition on "ability to write any code at all".
- ClumsyPilot 7y ago
  That is my experience too. I had people swear by "multiply two numbers without using a multiplication sign" as their one and only coding challenge.
- freedomben 7y ago
  Completely agree. Early in my career I had what I thought was "a suprisingly good filter" question I would ask candidates. For a couple years I did this. We hired one guy who had failed my question and then fired him a couple months later, which heavily reinforced my belief in the value of this question. A couple years later I found the stack of resumes, some we hired, some we didn't. I realized that some of our best engineers had failed my question, and I had subsequently voted "no" (I was outvoted, fortunately, by my fellow panelists. Another reason I strongly advocate panel interviews but that's a separate discussion). I soon realized that my question was good at filtering out people who didn't think like me, not at filtering out people who would make good engineers. My takeaway was that everyone, especially me, could use a healthy dose of humility and self-skepticism. Not advocating swinging the pendulum into Impostor Syndrome (which I now struggle with sometimes), but somewhere in the middle is good and healthy IMHO.
- mypalmike 7y ago
  I knew a guy, a really good engineer, who used to ask people about a specific thing you would see in a log file from a particular open source server. The SQL "having" question isn't quite as bad, but neither question is good at assessing one's ability to solve problems.
alasdair_ 7y ago
I’ve hand written thousands of sql queries, including a year spent writing nothing but PL/SQL stored procedures. I’d fail your test, not because I’ve never used it but because I’ve not had to use it in years and, importantly, I rely on Google to remind me of syntax trivia when moving back to a language I haven’t actively worked in in a while. I HATE trivia questions and I’m convinced they are a sign of a lazy interviewer. If you want to test someone’s knowledge of sql, give them a sample schema, an internet connected computer and five minutes to write an appropriate sql query for your use case.
- brightball 7y ago
  It’s not a trivia question. It’s directly related to GROUP BY and is a core part of standard SELECT syntax. I’ve never met somebody who has spent any amount of time writing raw SQL who didn’t know it. I’ve met a lot who overly rely on their ORM but “have done a lot of SQL” who don’t.
  - dennis_jeeves 7y ago
    Brightball, how many years of coding/sql/developer experience do you have?
    - brightball 7y ago
      Going on 20
      - dennis_jeeves 7y ago
        How much time would you think, the (+)average programmer, say with 10 years experience needs to learn _basic_ sql, along with the HAVING clause, assuming they have never learnt sql during their entire working career? ( we are talking RDBMS concepts from scratch. like Primary keys, normalization etc, and finally sql). (+) average= meaning the programmer should be able to say 'solve' the FizzBuzz under comfortable conditions. Or say add the first n items in an integer array etc...
ereyes01 7y ago
Trivia
amp108 7y ago
I went to a job interview recently where the interviewer was trying to phrase the question properly: "I'm trying to figure out how to ask this next one, so if you have a lot of Customers, and... umm..." And I interjected during a long pause and said "It sounds like you're trying to ask a question which could be answered by using a HAVING clause. So something like, only those Customers who have two or more orders, which would be 'GROUP BY Customers HAVING count(Orders) > 2'." I was right, he was looking for that. Still didn't get the job.

sitkack 7y ago

I like to structure my questions such that they could using HAVING or a CTE or subquery and not test on the piece of knowledge directly, but the application of knowledge. Like in Python if you asked lots of questions with list of numbers ... and then generalized it to infinite lists of numbers. If the first case I'd like to see list comprehensions, and then generator expressions or possibly the use of itertools.

brightball 7y ago
That tends to be how it leads in. We’ll talk through building a simple CRUD application with a single table and then ask questions about how to get specific data for somebody in marketing. There are a lot of ways to answer it, but HAVING is the simplest.

rockinghigh 7y ago

How would you know it’s a good filter? What position are you interviewing for?

kradroy 7y ago
It's about as good a filter as asking a candidate what a "gyascutus" is.
- FpUser 7y ago
  ROFL. You've made my day.

snarfy 7y ago

I can barely get candidates to join two tables.

deleted 7y ago
[deleted]

FpUser 7y ago

My use of SQL beyond very basic is very sporadic (I do remember about HAVING though). If you ask me out of blue I would most likely fail tests. From time to time however I do have to design database for particular product and in a matter of few days my knowledge gets from basic to very good one. Only to go down again a short while after database design implementation and testing completes. That does not mean that I do not know basic concepts about how different databases work, including underlying storage engines etc. To the point that for one product I personally designed actual NoSQL (EAV to be precise) database engine and query engine largely resembling SQL to go along. So would you fail me in your test?

freedomben 7y ago
Exactly. You don't use it, you lose it. That doesn't mean you don't have the capacity to reacquire it in a short period should you need it again. Most humans are not encyclopedias. I can describe for you in detail the different index types on MySQL and Postgres, and go into when/why you would use each. But it's been so long since I crafted a complex join by hand I would definitely have to google it. However I don't doubt that I could write the query in a short time. One of the most realistic interviews I ever had, they gave me a laptop and allowed me to google during my answer. I took the job.

kradroy 7y ago

Eh, HAVING is something that can be learned in 5 minutes. So filtering out for that is probably generating a lot of false negatives. I quiz them on indexes and table design. Those can be done without writing a single SQL query.

SkyPuncher 7y ago
I look up HAVING every time I write it. I just don't use it enough to keep it in memory as a first class concept. A quick google search with an example is enough to jog my memory. If I got asked about it on an interview, I'd be completely blank.
- babuskov 7y ago
  I'm curious now. What's there to look up? It's just like WHERE, but you use it on aggregate values. WHERE filters the data before you aggregate it, HAVING afterwards. That's all there is to it.
  - alasdair_ 7y ago
    Simply remembering that fact is language trivia that is easy to refresh when needed. It’s like asking if intel x86 is big-endian or little-endian. It’s trivial to check but if I haven’t had to use that fact in a while, I likely won’t bother to remember because it’s trivial to search for.
- ordinaryperson 7y ago
  Exactly. "I use [x] all the time and know it well, so [x] will be a good filter for evaluating someone's technical skills." No. People do different work tasks depending on the project or company. Sometimes I'm knee-deep in SQL. Others in React. I'm often forced to do PHP. My familiarity with specific SQL syntax ebbs & flows with what I'm doing. There's no point in keeping something in your brain if you're not going to use it every day. Just remember the general concept and Google the specifics when you need it. Just one example why interviewing is terrible and interviewers really should be trained on what not to do.

dadoge 7y ago

Is the pun of saying knowing “HAVING” is a good filter intended?

brightball 7y ago
It is now.

scarejunba 7y ago

My experience with interviewers is that they all believe that their pet question is "a surprisingly good filter". Considering that there is rarely a data-driven approach to recruiting, usually this statement has a heavy confirmation bias. Usually when you add data to the mix, you'll find that most questions are not significantly correlated with candidate success/failure once you condition on "ability to write any code at all".

ClumsyPilot 7y ago
That is my experience too. I had people swear by "multiply two numbers without using a multiplication sign" as their one and only coding challenge.
freedomben 7y ago
Completely agree. Early in my career I had what I thought was "a suprisingly good filter" question I would ask candidates. For a couple years I did this. We hired one guy who had failed my question and then fired him a couple months later, which heavily reinforced my belief in the value of this question. A couple years later I found the stack of resumes, some we hired, some we didn't. I realized that some of our best engineers had failed my question, and I had subsequently voted "no" (I was outvoted, fortunately, by my fellow panelists. Another reason I strongly advocate panel interviews but that's a separate discussion). I soon realized that my question was good at filtering out people who didn't think like me, not at filtering out people who would make good engineers. My takeaway was that everyone, especially me, could use a healthy dose of humility and self-skepticism. Not advocating swinging the pendulum into Impostor Syndrome (which I now struggle with sometimes), but somewhere in the middle is good and healthy IMHO.
mypalmike 7y ago
I knew a guy, a really good engineer, who used to ask people about a specific thing you would see in a log file from a particular open source server. The SQL "having" question isn't quite as bad, but neither question is good at assessing one's ability to solve problems.

alasdair_ 7y ago

I’ve hand written thousands of sql queries, including a year spent writing nothing but PL/SQL stored procedures. I’d fail your test, not because I’ve never used it but because I’ve not had to use it in years and, importantly, I rely on Google to remind me of syntax trivia when moving back to a language I haven’t actively worked in in a while. I HATE trivia questions and I’m convinced they are a sign of a lazy interviewer. If you want to test someone’s knowledge of sql, give them a sample schema, an internet connected computer and five minutes to write an appropriate sql query for your use case.

brightball 7y ago
It’s not a trivia question. It’s directly related to GROUP BY and is a core part of standard SELECT syntax. I’ve never met somebody who has spent any amount of time writing raw SQL who didn’t know it. I’ve met a lot who overly rely on their ORM but “have done a lot of SQL” who don’t.
- dennis_jeeves 7y ago
  Brightball, how many years of coding/sql/developer experience do you have?
  - brightball 7y ago
    Going on 20
    - dennis_jeeves 7y ago
      How much time would you think, the (+)average programmer, say with 10 years experience needs to learn _basic_ sql, along with the HAVING clause, assuming they have never learnt sql during their entire working career? ( we are talking RDBMS concepts from scratch. like Primary keys, normalization etc, and finally sql). (+) average= meaning the programmer should be able to say 'solve' the FizzBuzz under comfortable conditions. Or say add the first n items in an integer array etc...

ereyes01 7y ago

Trivia

amp108 7y ago

I went to a job interview recently where the interviewer was trying to phrase the question properly: "I'm trying to figure out how to ask this next one, so if you have a lot of Customers, and... umm..." And I interjected during a long pause and said "It sounds like you're trying to ask a question which could be answered by using a HAVING clause. So something like, only those Customers who have two or more orders, which would be 'GROUP BY Customers HAVING count(Orders) > 2'." I was right, he was looking for that. Still didn't get the job.