10 ms·
SQL interview questions are an interesting counterpoint to stereotypical programming interviews: while typical algo questions in SWE interviews tend to test wha
by minimaxir 7y ago
SQL interview questions are an interesting counterpoint to stereotypical programming interviews: while typical algo questions in SWE interviews tend to test what's taught in academic contexts but have little real-world application; the questions in SQL interviews are more practical outside the whiteboard.
A weakness of these types of SQL questions however is that it's near impossible for the interviewer to provide help/guidance; the questions are often know-it-or-don't questions (especially involving window functions w/ uncommon syntax). A data science interviewer years ago mocked me during a whiteboard test for not knowing the BETWEEN ROW syntax for a window function.
That said, as an IRL data scientist, the amount of times I've had to do a SQL self-join in the past few years can be counted on one hand. And the BETWEEN ROW syntax for a window function.
- WoahNoun 7y agoI use self-joins and windows constantly. It's way easier to have our massive redshift cluster do those calculations in parallel than to try and do it in python on a VM
- minimaxir 7y agoI do use window functions all the time; just not with BETWEEN ROWs. (to your other point, I work with BigQuery on the DB side, but R/dplyr is great at working with window functions as well in a much cleaner manner than Python/pandas).
- WoahNoun 7y agoI use between rows for rolling averages all the time. SUM() OVER (PARTITION BY columns ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) gives you a trailing week total.
- minimaxir 7y agoThat was incidentally the exact question I was asked. (When I asked during the interview "I know you need to use a SUM window function but I'm not sure how you constrain it to the past 7 days", the interviewer replied "you don't know the BETWEEN syntax? Everyone knows that." And then the interview ended.)
- lonelappde 7y agoYou dodged a bullet with that one.
- WoahNoun 7y agoDefinitely not cool to use syntax as an interview question, but it is a very powerful tool so I wouldn't write it off because of one interview.
- shostack 7y ago"In a real world situation I'd have googled it and jogged my memory in less than a minute. Since I suspect you were more interested in how I would approach solving X, can you help refresh my memory on the syntax and we can pretend I looked it up to get back to what's important here?"
- malinens 7y agosounds like he is some old guy who is afraid to loose his position and is gatekeeping so he can keep his current job...
- truculent 7y agoWhat a jerk. Sorry that happened to you.
- PeterisP 7y agoThis seems fishy - if your data has a whole day gap somewhere for whatever reason (e.g. I'm thinking sales data where certain emergencies might result in an unusual, unplanned 'zero day') then that query silently gives a wrong result, IMHO a trailing week total should always include a specific date boundary calculation to ensure that it's really a week.
- WoahNoun 7y agoFor the transactional DB sure, an analytic DB / data warehouse / or even just table would have a 0 record for the no sales day. If there are gaps in daily data, at some point they need to be filled in with 0s. You can either do that at report building or query time using a cartesian join or you can fill the nulls with 0s during the data engineering pipeline. A robust BI process should do the later. For example, if I want to plot this trailing calculation and it has gaps in the data, then the plot will be very wrong. The DE pipeline should fill in these gaps.
- artwr 7y agoTwo ways to address this: - Join to a date dimension and coalesce nulls to 0 - use RANGE BETWEEN (only available in some DBs) on a date_difference column
- beckingz 7y agoGoing from doing most of my analytics work in MariaDB/MySQL to Python/Pandas, and R/Dplyr, I'm amazed by how much trickier the syntax and operations are in Pandas.
- WoahNoun 7y agoYea, when I do use R, I end up almost always doing the data manipulations in sqldf (creates a sqlite db on the fly). But I do love doing vectorized operations in pandas/numpy. Writing a quick function then calling it against an entire numpy array is kind of fun.
- eyelidlessness 7y agoI have only in the last week had occasion to use `ROWS BETWEEN` in a window function, and once I did I immediately thought of dozens of places I could replace moderately complex imperative/functional code with a simple query. It's definitely not common knowledge and the expectation that you would know it should be very specific to certain skillsets, but damn once you know it, it can be a powerful tool.
- mycall 7y agoThe problem with SQL windowing functions (OVER ORDER BY, LAG/LEAD, RANK, etc) is that they are nondeterministic.
- WoahNoun 7y agoThey are only indeterminate when the SQL engine is given an ambiguous ordering. If you specify the ordering without ambiguity, it's entirely deterministic. Eg: indeterminate in -> indeterminate out but determinate in -> determinate out.
- steelframe 7y agoGiven the likelihood that I would have to work with the person who was interviewing me, I would count it a blessing that they show me their true colors during the interview rather than after I hire on. And if I didn't get an offer, I'd consider it a bullet dodged.
- ses1984 7y agoIf you do get an offer, and choose not to take it, that's dodging a bullet. If you don't get the offer there's no bullet to dodge, right? </pedandtry>
- _630w 7y ago<pedantry> Your pedantry tag has no opening </pendantry>
- buckminster 7y ago<pedantry> It wasn't a pedantry tag. And yours is differently misspelled! </pedantry>
- Hydraulix989 7y agoDon't start a sentence with a conjunction.
- steve_g 7y agoBut sometimes it's OK to break the rules.
- kmonsen 7y agoNot with -pedantic enabled, that’s kind of the purpose.
- Hydraulix989 7y ago
- thomzi12 7y agoSure, that's a fair criticism. That said, you can build multi-step interview problems with SQL (I tried to convey one or two in this doc) such that interviewers can build up towards needing a more advanced window function instead of starting there. I've used BETWEEN ROW maybe once or twice in my career in a professional setting. Self-joins more often, but as others have pointed out window functions are more efficient here for writing dashboard ETLs, etc. Btw, are you minimaxir who wrote gpt-2-simple? I was looking at your tutorial a month ago while putting together a solution for the Kaggle COVID-19 NLP challenge!
- minimaxir 7y agoyep! :) Glad you made use of it!
- lonelappde 7y agoWhy would you ask interview questions to quiz on skills that can be learned in a week? This is only useful if you tell candidates to study a book for a week first. Otherwise you are filtering for narrowminded memorizers, not smart people who can learn and solve problems
- thomzi12 7y agoOne thing I mention in the doc intro is that SQL questions are one part of the data analyst interview, not all of it. If this doc helps people get through the SQL questions so that they can spend more time being thoughtful with open-ended analytics questions, that's good thing (both for interviews and for work more generally)!
- golergka 7y ago> Why would you ask interview questions to quiz on skills that can be learned in a week? I'm not an SQL expert, but it's very important to understand whether something is a known unknown or unknown unknown to a candidate. So, I imagine, if the candidate would say something along the lines of "oh, this looks like something for a window function - I don't know the syntax and the nitpicks, but I could google it", it'd be completely OK. However, if he wasn't even aware that such a tool existed, it means that he would never go and learn it in a week without outside guidance, and would choose a less-fitting tool to complete a task.
- deleted 7y ago[deleted]
- onemoresoop 7y agoTheres no shame in knowing what it is/the theory behind a convept but not knowing the syntax, especially if the interview is not for a DBA. Id be honest and ask to look it up online or move on..
- xivzgrev 7y agoThen you’ve only encountered shitty interviewers. The point isn’t if you know some syntax, that can be easily taught / googled. The point is how you think about the problem. In the case of percent change in MAU, you don’t need to know how to do a self join, exactly. But rather to identify that you would want to do a self join and on what conditions would be the key
- WoahNoun 7y agoYea, I ask the employer/manager query problem sometimes. I'm not looking for the a right answer, but rather how they think through the problem and how they think about depth bounds. Occasionally I'll have a candidate who can throw out an oracle "connect by," but it is absolutely not what I'm looking for. (Even though that's probably the "most correct" answer).
- vsareto 7y agoI'd say it's unlikely you know about self joins but don't know at least one syntax for it just because of how we traditionally teach SQL to others. This is the know-it-or-not part: SQL is rarely taught without syntax and syntax is used to illustrate the theories. This is in contrast to regular programming where people just pick up pseudo code eventually. If you had a pseudo code for sets, joins, etc, it'd probably be using more mathematical symbols.
- JohnTHaller 7y agoI've been using SQL for 24 years and don't think I've ever used BETWEEN ROW before. Did read up on it now, though.
- psaux 7y agoI wrote sql for years for a forex platform used by some of the top dogs (literal). Pretty hardcore sql and had to be very fast, never used BETWEEN ROW.
- roel_v 7y agoWhat breeds of dogs use forex platforms?
- guywhocodes 7y agoFiat Retrievers
- thom 7y agoI use it all the time, both in ad hoc queries and in data prep for models. Most commonly just to look into a future window from the current event (i.e. did this pass in a game of soccer lead to a shot etc). It's quite rare that I'll specifically care about X rows preceding or following but it does happen that you care about a shorter period than the entire window. Entirely possible people are using graph databases for that sort of thing, but the window syntax is nice.
- markus_zhang 7y agoI have to use it quite often because the dialect I'm using defaults on BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW which I have to manually change to BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
- nogabebop23 7y agoThere are so many specialized aspects that you'll never use outside of an interview or an edge case when you can learn it on-demand.
- megaframe 7y agoI think it depends on your data / query patterns. Self Inner/Left/Right joins are one of my most common queries. I often need to chain joins in various ways to get the desired output.
- crazygringo 7y agoI've never heard of BETWEEN ROW, but self-joins have been a pretty common thing for me, particularly when you're doing analysis on any kind of business-centric table that (rightly) has tons of columns -- e.g. a "users" or "products" or "items" or "orders" table.
- noelsusman 7y agoThat's pretty funny. I've been doing data science for ten years or so and I've never heard of BETWEEN ROW before.
- truculent 7y agoI don’t think it’s fair to say these examples are know-it-or-not. Sure OP has presented some example solutions, but most of these solutions can be built up from the basics of tabular data manipulation. Furthermore, there are multiple steps involved that would allow a candidate to show their grasp of these fundamentals and receive help from an interviewer (that doesn’t trivialise the problem).
- jlj 7y ago> A weakness of these types of SQL questions however is that it's near impossible for the interviewer to provide help/guidance I've given SQL interview questions quite a few times and like to present a sample dataset or schema, and state a problem statement or expected result. Then I collaborate with the candidate to come up with assumptions, and I give gentle guidance if they are stuck or going down the wrong path. I'm always up-front with the candidate about not looking for perfect syntax, and I am more interested in their problem solving and collaboration skills than the actual SQL they write. People who ask ask questions about the data and schema usually do much better than those who jump right into the solution. Just like in the real world where really nice SQL or code doesn't matter if it solves the wrong problem.
- ignoramous 7y ago> A data science interviewer years ago mocked me during a whiteboard test for not knowing the BETWEEN ROW syntax for a window function. Sad. One needs a thick skin to interview, because the manner of rejection is far more detrimental than rejection itself. I have seen friends cry unable to believe their luck or themselves. Often, due to negative bias, it takes a lot of confidence away from an individual and they start to feel inadequate, doubly so, if they had prepared super hard and yet failed because they couldn't remember a trivia. One of the best interviewing advice I got from a mentor was, interviews aren't a pissing-off or a dick-measuring contest but often are. It changed the way I approached the interviews as an interviewee. Also, a good thing that I seeked for and got this advice way before I started interviewing candidates.
- geebee 7y ago"mocked me". That's really reprehensible. One point I've made repeatedly here on HN is that technical "interviews" have morphed into a form of entrance exam with very little oversight. If you read about entrance exams at older institutions and professions with entrance exams (the bar, medical and nursing boards, actuarial exams, and so forth), you'll find that a they are often considered among the more stressful events in a person's academic career. They are often (and should be) very rigorous, but I do think a certain unspoken bill of rights has emerged to protect the student as well was the people conducting the exams. For example, entrance exams should have a study path, a known body of knowledge that is getting tested. They should be graded consistently and fairly, by acknowledged experts in the field. There should be a way to file a grievance, and the evaluation metrics must be "transparent" - if not the specific deliberations, then at least the general approach. Tech interview exams have none of this. They are conducted very, very capriciously, often by people who have limited skills and experience - even if they are experts in their field (which they often aren't), they may not have any idea how to evaluate a candidate. One basic tenet here is that you don't "mock" a candidate. Seriously, wow.