Given 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.

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

<pedantry>

Your pedantry tag has no opening

</pendantry>

<pedantry>

It wasn't a pedantry tag. And yours is differently misspelled!

</pedantry>

Don't start a sentence with a conjunction.

I'll terminate this by being so pedantic that we cross over into non-pedanticism.

Per descriptive linguistics (https://en.wikipedia.org/wiki/Linguistic_description), the assertion that it's wrong to start a sentence with a conjunction is incorrect. Ditto for splitting infinitives, ending a sentence with a preposition, etc.

(Descriptive linguists basically define the rules of language to be how humans actually use the damn language. It turns out such a philosophy saves you from falling into a lot of traps)

Unfortunately merely noting 'descriptivism' as if it solves the issue of linguistic prescription doesn't help that much. We prescribe all the time, like it or not, language isn't a free for all at the individual level. It's also _descriptively_ true that many people regard certain rules as inviolable, which certainly means that prescription is, in some sense, completely reasonable: comply, or we'll refuse to accept you. How does 'I'll do whatever I damn want' really handle that? It can be true that it's 'valid' on one level, while being wrong on another.

I immediately dismiss as gauche and lacking credibility anyone who even hints at a prescriptivist rule, so my acceptance goes against prescriptivism. These rules make writing unclear, stolid, dull and uninteresting for no real benefit.

In my opinion, parroting prohibitions against split infinitives, ending with a preposition, "snuck is not a word", "'they' is only plural", etc etc not only shows a profound lack of education, this shows an inability to learn, since these so-called "rules" are widely and easily known to be made up whole cloth. To my mind, this is a person who absorbed Elements of Style (perhaps even second-hand from a misinformed high school teacher) and called it a day, believing they understand how English is properly written.

Serious red flag for me.

"I immediately dismiss as...lacking credibility anyone who even hints at a prescriptivist rule"

Isn't this a prescriptivist rule? Imagine a highly skilled author who recognizes a younger one that misuses a semicolon or has poor grammar; the older says to the younger, "only write with perfect grammar". By doing so, the younger learns more precisely the elements of the English language and the purpose of each device (semicolon, period, comma, etc.). After, the younger becomes a much better writer, and integrates that new knowledge with his or her existing writing. In this way, prescriptivist rules help inexperienced writers hone in on their weaknesses and draw focus to some facet they lack.

Any harsh prescription is kind of a red flag for me, because you have to judge yourself by that harshness (fall on your own sword, so to speak). Or, as what usually happens, hold some hypocritical double standard or semantic loophole which pardons yourself while incriminating others.

<< Imagine a highly skilled author who recognizes a younger one that misuses a semicolon or has poor grammar; the older says to the younger >>

I cannot imagine a highly skilled author who never employs split infinitives. Show me that person.

Regarding semicolons, here is Kurt Vonnegut's opinion on them: "Here is a lesson in creative writing. First rule: Do not use semicolons. They are transvestite hermaphrodites representing absolutely nothing. All they do is show you've been to college."

Is that good advice? Perhaps for some writers, it actually is; but it's not about the semicolon, really, but about stilted, bad writing.

<< Isn't this a prescriptivist rule? >>

I was responding to the previous comment "It's also _descriptively_ true that many people regard certain rules as inviolable, which certainly means that prescription is, in some sense, completely reasonable: comply, or we'll refuse to accept you." My direct answer is "It's the prescriptivists 'refusing to accept' who should worry about acceptance, since they are failing on so many levels it's hard to know where to start"

Agreed. Elements of Style is full of errors, some of which are so obviously wrong that even its own authors don't follow them.

Do you have any good recommendation for improving writing skills?

I picked up elements of style few days ago. I couldn't parse it. I was only able to understood it somewhat through intuition and prior knowledge. The sentences were too confusing, out of the place for me with added jargon and story of the author that I didn't want to know about.

How many pedants are descriptivists?

Few

But sometimes it's OK to break the rules.

Not with -pedantic enabled, that’s kind of the purpose.

Unless you're already calling someone else out for a spelling mistake.

The most reliable way to dodge bullets is to not have them fired at you in the first place.

Unless you know a guy, you have to wade through the firing range to land a competitive job.

Sometimes you need to wade through the firing range as part of your job:

https://nypost.com/video/reckless-police-instructor-slammed-...

I've always preferred deflecting them with my katana :)

It depends on whether you consider "dodge" in the phrase to be active or passive.

Dodging taxes is active. Dodging the in-laws because you were in the shower when they dropped by is passive.

Both applications of the verb are in common usage and are therefore valid and obvious enough that you clearly understood the meaning. </pedantry>

"Dodged a bullet there" is often used in hindsight, where you find out something was terrible after you avoided it for a different reason.

Never thought of this, but you are right. Usually it’s used when you didn’t know you were dodging the bullet at the time but more of a hindsight thing.

Exactly

I'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.

I 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.

What breeds of dogs use forex platforms?

Fiat Retrievers

I 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.

There 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.

I 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.

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

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

Sure, 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!

yep! :) Glad you made use of it!

Why 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

First, from someone with 25 years of SQL development experience, let me say while it may be true these skills could be learned in a week, most are not learned at all even by regular practitioners.

And as with all such questions I'm judging your comfort with the concepts behind the skills - in this case set theory, conditional logic, data modeling, etc.

So it's more how you say it, not what you say it. And yes it's mostly negative signal - most interviews are just to weed out completely hopeless candidates.

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

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

Theres 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..

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

Then 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

Yea, 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).

I'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.

I 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.

I'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.

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

That's pretty funny. I've been doing data science for ten years or so and I've never heard of BETWEEN ROW before.

As if "data science" would be a field where the full scope of SQL is covered. SQL was developed for the enterprise world.

I'd be either suspicious or very jealous of a data scientist who in any reasonably long career never had to touch SQL themselves.

I 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

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

I 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.

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

"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?"

You dodged a bullet with that one.

Definitely 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.

What a jerk. Sorry that happened to you.

sounds like he is some old guy who is afraid to loose his position and is gatekeeping so he can keep his current job...

This 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.

For 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.

Two 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

I 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.

Going 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.

Yea, 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.

The problem with SQL windowing functions (OVER ORDER BY, LAG/LEAD, RANK, etc) is that they are nondeterministic.

They 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.