4 ms·

I am currently interviewing a lot of data engineers and I use the following question: Given a table that contains a history of spot readings from many differen

I am currently interviewing a lot of data engineers and I use the following question: Given a table that contains a history of spot readings from many different sensors readings(sensor_id bigint, reading_ts timestamp, reading_value decimal) show me the latest known reading for each sensor. Things I look at: 1. Does the candidate immediately reach for a window function? 2. Can they do it without a window function? If the candidate fails at this stage, it's a no-hire, then I can use the same task to check their seniority. 3. Do they actually know the right window function? 4. Can they explain why a window function is better here than grouping in a subquery? 5. Can they do it in an imperative language? If they can answer at least two out of three, they are a solid middle. 6. Can they suggest a way to store the data that makes the query take O(number of sensors) time? Okay, this is a senior engineer.

danwee 3y ago
Funny enough, I would fail your interview. But not because I don't know how to solve the problem, but because I don't know how to solve the problem without Google and/or documentation. My struggle is: I can come up with some working solution by myself, but I always know that my solution is "not the best", so after "solution v1 made entirely by myself", I jump into Google and find out what's "the best way to solve my little problem". After a few iterations, I come up with "solution v4 by myself and the help of the internet" which turns out to be way better than v1. My v1 would not pass your interview. My v4 would be a world-class solution. Fair enough, you are not looking for my v4, and rightfully so I should fail your interview (because I bet there be some people out there who can produce world-class solutions without relying on external sources)
- mraza007 3y ago
  I would definitely agree with you. I can’t do without documentation or google. And that makes me think am i a bad programmer
  - phatskat 3y ago
    > And that makes me think am i a bad programmer You’re not. We all need documentation and google, and anyone who claims otherwise will eventually shoot themselves in the foot. I’ve definitely given interview questions my best shot and admitted where I’d defer to the internet.
clusterhacks 3y ago
We use similar questions with the exception of #6 - our data sets are small enough that just getting a correct query is fine. We add a small relational design exercise to see if they understand data modeling 1-to-N and M-N relationships. Totally curious - what is your fail rate like for applicants at the first stage for the window function? We are happy with more junior engineers who usually haven't used a window function but can currently do simple queries and an easy candidate to move forward with will come up with a group by subquery . . . I'm guessing that 2/3 to 3/4 of our in-person interview candidates look (on paper) like they could handle simple queries and grouping but struggle in the interview context.
- orthoxerox 3y ago
  I am currently interviewing junior engineers (recent graduates with little experience) and among them about 50% say, "oh, I know this has something to do with window functions", but I usually warn them it can be solved without them. About two thirds of this group the solve it via a subquery vs about one third of the other group. Practically no one so junior can do it via a window function.

danwee 3y ago

Funny enough, I would fail your interview. But not because I don't know how to solve the problem, but because I don't know how to solve the problem without Google and/or documentation. My struggle is: I can come up with some working solution by myself, but I always know that my solution is "not the best", so after "solution v1 made entirely by myself", I jump into Google and find out what's "the best way to solve my little problem". After a few iterations, I come up with "solution v4 by myself and the help of the internet" which turns out to be way better than v1. My v1 would not pass your interview. My v4 would be a world-class solution. Fair enough, you are not looking for my v4, and rightfully so I should fail your interview (because I bet there be some people out there who can produce world-class solutions without relying on external sources)

mraza007 3y ago
I would definitely agree with you. I can’t do without documentation or google. And that makes me think am i a bad programmer
- phatskat 3y ago
  > And that makes me think am i a bad programmer You’re not. We all need documentation and google, and anyone who claims otherwise will eventually shoot themselves in the foot. I’ve definitely given interview questions my best shot and admitted where I’d defer to the internet.

clusterhacks 3y ago

We use similar questions with the exception of #6 - our data sets are small enough that just getting a correct query is fine. We add a small relational design exercise to see if they understand data modeling 1-to-N and M-N relationships. Totally curious - what is your fail rate like for applicants at the first stage for the window function? We are happy with more junior engineers who usually haven't used a window function but can currently do simple queries and an easy candidate to move forward with will come up with a group by subquery . . . I'm guessing that 2/3 to 3/4 of our in-person interview candidates look (on paper) like they could handle simple queries and grouping but struggle in the interview context.

orthoxerox 3y ago
I am currently interviewing junior engineers (recent graduates with little experience) and among them about 50% say, "oh, I know this has something to do with window functions", but I usually warn them it can be solved without them. About two thirds of this group the solve it via a subquery vs about one third of the other group. Practically no one so junior can do it via a window function.