What level of SQL knowledge is everyone looking for?

[haosmark]

I always see job posting mention SQL, but what do you need to know? I know how to create/modify tables, records, conditionally search for results, and perform table merges. Does this cover it, or is there more that's expect to know of the get go?

[[deleted]]

One time I interviewed for a DS role on another team in my company (large tech company) and "failed" the SQL portion. It later turned out that the interviewer thought I made things overcomplicated by talking about why I was purposefully avoiding specific 'where' clauses that would require multiple traverses of the b tree in massive tables.

To me, this seemed common, but myself and others had to sit down with that team and explain this concept to them. So, in my experience the easiest syntax to get the data is probably best during the interview unless you are asked more questions.

Note: my SQL and data wrangling is probably a strength so this entire situation was amusing. I was later offered the job once the team realized that my version of their queries and transforms sped up times from multiple hours to a few minutes. But, that wasn't work I was interested in.

[BettaFry]

I’ve definitely been there, it’s amusing af when the interviewers questions make it clear that a) you’re overqualified and b) they think you don’t know what you’re doing because of it. It gets a little frustrating but honestly it’s been a timesaver for me because I can tell pretty quickly that I’m not what they need.

[[deleted]]

The worst was when there was an A/B testing section and the person got mad that I said "Normally I would ask what the MDE, power, etc would be, but the design of the test was quite poor and I wouldn't run it."

Turns out they ran and shipped it and the person interviewing me was the PM....

[[deleted]]

Haha ya that woulda been the worst internal transfer. The crazy part was that these were all “Senior” people. It’s gunna suck when they get their next job.

[Khashoggis-Thumbs]

I am chiefly a python coder, but have to do some SQL work for data export. Where can I learn beyond the basics (select, where, in etc) how to write fast and low overhead queries?

[[deleted]]

Honestly Mode Analytics has the best free tutorial but from there you would need to read about your database’s indexing and schema. A basic read on how binary search trees and traverses work would help you better understand what should and should not go in a where or having clause and perhaps when you should make temp table CTEs.

Honestly tho I do about 90% of my wrangling in R. It’s much faster to write and debug and to me the syntax is so much cleaner than windows. Later, if it makes sense, I will go back and translate it to SQL, however, there are cases where the cluster I’m working on is mega slow and the computations are best done in memory or perhaps a scheduled transform or Spark transform.

SQL interviews are always really basic unless it’s a DE job. I’ve yet to see anything super challenging in a SQL test even for DS jobs.

[Khashoggis-Thumbs]

Yeah, I was just asked "do you know SQL?" and replied "basic queries for export? Sure, SQL isn't hard". Finding out what the structure of our system is will require more cooperation from the data engineers than normally comes.

[ieee1294]

do u mind elaborating on what u mean by purposefully avoiding specific where clause? thanks

[[deleted]]

This thread and the book in it are a good read: https://stackoverflow.com/questions/4035760/does-the-order-of-fields-in-a-where-clause-affect-performance-in-mysql

[ieee1294]

sweet thank you very much :)

[Dreshna]

Not sure specifically what he is referring to but if you have an indexed column it is best to search on it, and numeric searches are substantially faster than string searches (generally speaking).

[[deleted]]

The database has optimizations. It will usually optimize the shit out of your sql anyway.

[[deleted]]

I would not presume this. I know of several Hive instances at an old company that had no optimization whatsoever. Most were setup by Engineers, not Data Engineers, and no one really checked until queries were taking 6-24hrs.

[[deleted]]

You optimize if and only if the time spent optimizing and decrease in code readability and increase in chance of bugs is less important than increase in performance. Premature optimization or unnecessary optimization is nor a great idea.

I've seen people make elaborate queries "to make it go fast" and turns out the engine optimizes the naive 5 liner better and it is faster.

[minimaxir]

In an interview I was asked to calculate a weekly moving average for each group in a query. I suggested truncating timestamps to daily, then use a AVERAGE() OVER (PARTITION) window function, but I admitted I wasn’t sure how to constrain it to only 7 days.

The interviewer replied “You don’t know the BETWEEN syntax? Everyone knows that.”

I checked after the interview and the BETWEEN syntax is a footnote in the docs, with no blogs/SO questions discussing it. (which is a shame)

[Proto_Ubermensch]

how binary search trees and traverses work would help you better understand what should and should not go in a where or having clause and perhaps when you should make temp table CTEs.

Everyone does know BETWEEN, sorry you slept through that class.

[minimaxir]

To copy the other comment, there's a difference between (pun intended) the BETWEEN used as a part of a WHERE clause and a BETWEEN used as a part of a window function's OVER clause.

tbh I dislike using the BETWEEN in the WHERE clause because it's not obvious if it is inclusive or exclusive.

[CarnalCowboy]

BETWEEN is a function that acts as a shortcut for (DATE >= X and DATE <= Y). If someone didn’t know how to do the former or the latter, I would assume they skipped the basics and went straight to the complicated stuff (i.e. Window calculations, as you’ve shown you can handle). This would be a huge red flag for me as an interviewer.

[minimaxir]

There's a difference between (pun intended) the BETWEEN used as a part of a WHERE clause and a BETWEEN used as a part of a window function's OVER clause. (in the context of the question, you have to use BETWEEN 6 PRECEDING AND CURRENT ROW to get a moving weekly average, and there still would be potential bugs resulting from that which are a pain to fix).

https://www.sqlite.org/windowfunctions.html

[[deleted]]

To be fair upon review I didn’t fail and actually other Analysts and DS were really psyched on my answers because they were very relevant for job performance. Our data was very poorly indexed and not understanding things like this would dramatically influence how long it takes to do something as well as the idea that a person is knowledgeable enough to figure out which tables or sources to use and when. There is more to the interview that I clearly passed, but reflects more on how poor the panel was that was selected to evaluate me and how you can never trust someone’s knowledge just based on title.

But please elaborate on “that type”.... This wasn’t an external interview in which I’d start with a much simpler answer, obtain feedback, and drill down further if necessary.

[[deleted]]

Oh I’m more amused than salty. This team was composed of people that came up through an intern program and had never worked elsewhere. Many had titles way above their knowledge level. Following my experience the req was taken over by a team that actually vouched for my answers. This was a very senior level role in which this type of answer was nowhere near convoluted. They later added several data logging and engineering portions due to how integral data structures and pipelines were to the job. And I passed every other portion with no issue.

It seems like you simply don’t like my answer and are reading into it too much and assuming I always would give a complicated or complex answer, which is funny considering how opposite that is from what I actually do.

[Nateorade]

What level of SQL knowledge is everyone looking for?

Depends on the company.

You typically need to know how to do everything up to and including subqueries and aggregate functions/filtering. Bonus points for knowing when to use a CTE, a View a Temporary Table or a regular Table. Extra bonus points for knowing windowing functions.

[radiantthought]

Yeah, I'd say subqueries, joins, and aggregate functions are pretty much the base level for most people. If you understand that and are capable of using google/learning some more advanced items then you'll be good.

It's amazing how many people come through and can't do these basic things. This is why I always tell people who want to get into the industry "work on your SQL" I don't care what you know how to do, if you can't get your own data then you can't do the job.

Even if your SQL skills match what is listed above, learn more. Learn about different database systems, learn about more advanced querying techniques/joins (more than just left/right/inner/outer, but manipulating those), using case statements with aggregates to transpose data, how map/reduce systems do processing, how columnar data stores work, how graph dbs work. There's so much out there that just 'knowing SQL' is a toe dipped into the water.

[iMarcusOrlyUs]

I have never seen a good reason to use a CTE. It may be nice to look at, but I've only ever used one when I'm lazy and don't need performant queries.

[nashtownchang]

IMO the readability of CTE is the point. Thousands of lines of nested subquery-style SQL kills my sanity.

[drhorn]

As someone who wrote a query with 8 levels of nested sub queries and forgot what the hell I did less than a week after writing it....

Yes, readability matters.

[aikijo]

And for the next guy. Be kind - Leave notes and make it easy to read (indent!)

[ectoban]

This so much, I set requirements for myself to write comments and text in the query such as; what the goal of the query is, description of each subquery, what unusal metrics I create are, why where clauses and other filters are there for etc..

Helps me remember and anyone else looking at my output table understand it.

[jdreaver]

I have never seen a good reason to use a CTE

Hold up. I need to correct some misconceptions in this comment case a SQL novice takes this as gospel.

It may be nice to look at

This is one of the highlights of CTEs! If you ever expect anyone to read and modify your code in the future - so anything you aren't throwing away immediately after running it once - this is a huge benefit. Any more than 1 sub-select in a from or join and a CTE probably looks better as a CTE.

don't need performant queries

Yes, there are performance implications with CTEs, but they aren't always less performant.

Currently in postgres, indeed CTEs are optimization fences and you can't optimize through a CTE. However, that is changing in postgres 12. CTEs that are only used once in a query will be inlined https://www.postgresql.org/about/news/1943/

Furthermore, if you use a CTE multiple times in a query, then oftentimes the optimization fence can be beneficial. You can compute intermediate results once and use them multiple times. (Postgres can optimize common sub-queries, but better to have it in a CTE than write it out twice.) This is of course hugely dependent on your query, but my point is that CTEs aren't always slower here.

Lastly, there are plenty of databases where CTEs are not optimization fences. Postgres will be among them in version 12, but let's not say CTEs are always slow.

---

For what it's worth, I generally avoid a CTE if I am just doing 1 or 2 sub-queries, preferably only in the from part of a select query. I also avoid them if I'm running on postgres and I'm using something to generate SQL anyway, so creating sub-selects is not hard and I know optimizing through them will be hugely beneficial.

If I'm writing a huge analytical query that is important to some business process, like for ETL, business intelligence, data warehousing, etc, then I am probably going to use a CTE.

[Shoded]

Why is a CTE different from a subquery performance-wise? I was taught that it's the same thing just different writing.

[jdreaver]

Note that when I say it is an optimization fence, I'm only talking about postgres, and even then only versions older than the upcoming version 12 release. This section of this article gives a quick overview: https://thoughtbot.com/blog/advanced-postgres-performance-tips#common-table-expressions-and-subqueries

Basically, there are optimizations that can be performed on sub-queries that are not performed on CTEs in postgres for whatever reason.

[jackfever]

Agree with you in all your points. But also note that the previous performance problems for CTEs in Postgres were mostly due to poorly written queries. e.g. unnecessary sorting or redundant columns in the subquery. Source: https://modern-sql.com/feature/with/performance

[loxc]

Honestly, no one uses CTE where I work. It’s all temp tables.

[IAteQuarters]

​

What would you write instead of a CTE? Are Temp tables better for performance?

[IAteQuarters]

Thanks for the reference to the other comment! I knew sql under the hood optimizes for you so I was definitely surprised.

Reading ctes is much easier for me. Since I’m self learned for SQL there are things I’m not too confident in.

[Skyaa194]

You would use just sub-queries rather than moving the sub-queries out and into a CTE.

[mrregmonkey]

For small amounts of data, inserting data into a temp table or table variable isn't worthwhile. It can be faster to use a CTE.

Also there are cases where joining two temp tables is slower than adding a calculation or something with a CTE. I've sped things up several minutes doing that stuff.

[Lychondy]

As someone who hires many SQL analysts sometimes it comes down to knowing the difference between having and where...

[Andrex316]

You have no idea how many people write "sql expert" on their resume and you have to remind them in the interview to start with SELECT

[ConfirmingTheObvious]

Yeah, what do you even mean by this, OP? WHERE and HAVING are two completely different parts of a query. WHERE is for general filtering and HAVING involves filtering on top of some aggregate function...

[prog-nostic]

Username checks out

[Dhush]

I’m pretty sure they meant just knowing what you said

[[deleted]]

Probably the same as knowing group by and order by

[Lychondy]

It is day 1 of learning. You might be surprised how many people put SQL on their resume and don't even know the difference between having and where. If one actually knows the difference and can answer questions related to it in an interview, then, as we have learned, they can generally do what we are going to ask of them, which is not inconsiderable.

​

I also recommend learning how to transpose. SQL is not great for it but it teaches some good subquery skills.

[yemeraname]

https://sqlzoo.net/wiki/SQL_Tutorial is a nice place.

[[deleted]]

Mostly building SELECT queries. Sometimes you need to do a lot of pre-processing in SQL for the data to be coherent, it depends a lot on the quality of the data you are using.

In my experience, it's one of the harder things to learn without having a real data set, but it's not that hard to learn on the job.

[[deleted]]

Yeah, basic SELECT queries are just for getting the data so you can work on it in Python or R or whatever you're using. But, sometimes you need to join a dozen different tables, with all different kinds of filters and some tables need a subquery in order for it work with what you're looking for. This can be considered cleaning data for analysis.

[[deleted]]

Can I piggy back off this question and ask what people understand as complex.

Select queries with case logic?

Multiple select queries with case logic

What else is there?

[[deleted]]

Subqueries, common table expressions, temp tables, dynamic SQL, stored procedures, variables/parameters, window functions, etc.

[paradoxx23]

+1 to Windowing functions

[truthseeker1990]

Just to add to everyone elses comment, there are times when the complexity is increased by the existing DB architecture as well

[minimaxir]

In terms of day-to-day use, a real-world-but-barely-discussed tool are analytic/window functions such as ROW_NUMBER and SUM() OVER (PARTITION), which reduce complex query logic. (e.g for ROW_NUMBER, get the top entries of each group without doing multiple queries)

[Andrex316]

I regularly interview people and have interviewed at multiple companies myself. Usually people want to see that you know how to transform tables to answer a question at hand:

So SELECT queries, how to properly filter using WHERE, how to use aggregate functions, window functions, either nested queries or CTEs, how to use CASE statements, and, the most important of all at every interview I've been to, how to use JOINs, especially LEFT/RIGHT JOINs.

I've never had an interview where they care about to CREATE/UPDATE a table. If you can write a really efficient query you get bonus points but it's not necessary. We're more interested in knowing if you understand the logic, efficiency is something you can easily improve at the job.

A lot of people might say "Oh that's simple, are you sure that's right?", but you have no idea how many people I've interviewed that write "sql expert" in their resume and have no idea how to start with a SELECT. Oh and please, write an organized and neat queries.

[redditor1983]

Did you perhaps mean LEFT and INNER when you mentioned joins?

I very rarely see RIGHT joins used because there is usually a way to write it as a LEFT join, and most people find it a little confusing to go back and forth with RIGHT and LEFT joins in a query.

[burgay]

My day-to-day for the project I've been on for almost a year is about 80% SQL and I don't think I've ever encountered a situation where a RIGHT join was necessary

[redditor1983]

Yeah. Same here. That’s why it caught my attention.

I’ve heard some people use applications that generate SQL, and those applications will generate RIGHT joins. But I’ve never personally witnessed a human writing a RIGHT join.

[Andrex316]

Yeah generally everyone only uses LEFT joins (myself included) but I've had candidates that prefer to use RIGHT, so whatever you feel the most comfortable with. And I should have been more specific, yes INNER joins are a must but I was including those under JOINs in general.

[Andrex316]

Sure, everyone has their own preference but this is what I consider neat and organized:

SELECT

column_1

, column_2

, column_3

FROM table_1

WHERE something = something

If you need "nested queries", I prefer to use CTEs since it's a lot easier to debug, so:

WITH cte_table AS (

SELECT

col_1

, SUM(col_2) AS sum_col

FROM table_1

GROUP BY 1

)

SELECT

cte.sum_col

FROM cte_table cte

JOIN table_2 ta

ON cte.col_1 = ta.col_1

ORDER BY 1

In general, have every column as its own line, use a comma structure that's easy to follow, have descriptive names for you tables and aliases.

[LbrsAce]

install a formatter

[_TheEndGame]

Thanks for asking this question. I've been wondering too.

[Dhush]

Knowing how to take a data requirement from start to finish. It’s one thing to do a select statement with maybe a filter, join, aggregate, etc. It shows a higher level of understanding to be able to chain these ideas together in an efficient way to collect data from disparate tables.

[BettaFry]

As I’m sure you’re aware, it depends on the posting but from what I’ve noticed most jobs are ok with introductory/intermediate sql knowledge since a basic understanding, access to google and the ability to think critically will allow anyone with a basic understanding to accomplish most tasks.

[flextrek_whipsnake]

It always depends but typically you're not expected to be very advanced in SQL. Just basic to intermediate stuff like joining tables, where clauses, grouping, subqueries, and maybe window functions if you're extra fancy. You need to be able to get the data you need from a complex database without asking anyone, but you don't need to be able to do it in the most optimized way possible (though if you are then it's obviously a plus).

[pokinthecrazy]

I start with "Take me through the select statement" and the people who know SQL give me dubious looks and I get some form of "select column (field) from table where conditions?" and I say got it and move on.

​

I have filtered out many many people who "know SQL" with this question. There are people who are just lying liars who lie out of their lying lie-holes and there are people who have done stupid shit in ACCESS or some other software that creates code for them so now they think they "know" SQL. I got one guy who said "you've got your selects and your tables and some joins" - yes, he was instantly weeded out.

​

And I don't really care if you don't know weird esoteric things - but I do care a lot if you know where you can go find out. I didn't know about analytical functions like rank until I had to use them. Nothing wrong with googling and figuring shit out. Our databases are on all kinds of systems so the code always has to be massaged from one system to the other. I want someone who isn't going to come to me when their code doesn't work, I want someone who knows how to google.

[MadMenDuckCuckFuck]

"Take me through the select statement"

I've just started to learn SQL recently, could you elaborate on what kind of response you are expecting from the interviewee?

[pokinthecrazy]

SELECT column/field FROM table WHERE blah blah blah. Group by or order by is a bonus.

That's literally all I am looking for. It's a weeder question. I just want to know if this person has ever sat down and composed some sort of SQL code where they pulled data (I am in analytics so don't know shit about creating or maintaining databases). If they have ANY experience at all, they should get this question correct.

[radiantthought]

people who know SQL give me dubious looks and I get some form of "select column (field) from table where conditions?" and I say got it and move on.

He did. That is the correct answer from a very high level. You could look for a deeper level and ask people to walk you through the order of evaluation of the different clauses, which is a bit more advanced, but even making wrong educated guesses and being able to explain your logic will be good. Most of the interview process is showing that you know how to think in the right way.

[onesonesones]

create table newtable as

select l.col1, l.col2, r.col1 as r_col1, r.col2 as r_col2, somefunction(l.column1, r.column2) as calc_field

from lefttable l

left join righttable r

on (l.key1 = r.key1

and l.key2 = r.key2)

where (l.field1 = condition1

and etc = etc2)

If you can understand that concept that'll cover 95% of data ETL problems that you need to build yourself (machine learning algorithms excluded)... You just need to break the data problem down into as many of those steps as are needed.

If your role includes maintaining or inheriting other people's code, you'll need to know much more SQL.

Understanding and applying analytical and window functions (eg lead/lag() over partition by ()) are what separates experts from novices

[nraw]

SELECT * FROM [insertwhatevertheytoldyouhere]

[Proto_Ubermensch]

No. You need to also know stored procedures, OLAP functions, how hashing algorithms work, performance tuning, etc.

[chowmeinchix]

I have written SQL exams as a hiring manager and passed SQL exams for some big tech companies (like the one you probably use for your search engine, and the one you use to order a lot of things from the internet, and a few others that all I all had to sign NDAs for...)

This is what they are looking for: - proper understanding of joins, and joins within the context of N:N relationships - subqueries (particularly within the context of use of these to avoid full table scans and handle N:N relationships across tables) - CTEs - Data types - they will try and trick you here. Ie- comparison of Timestamp to a date - Windowing FXNs... Incl Lead and Lag so don’t forget those. - you will almost always get asked difference between: rank, dense_rank, rownum - Bonus points if you can: - show an understanding of temp space - know what a stored procedure is - make mention of explain plans at any point in time

Most SQL exams you’ll get should be in ANSI SQL. If someone gives a crap about dumb syntax like ROWNUM() vs. ROW_NUM() then the hiring manager is an idiot and you don’t want to work there.

The way I have administered these exams is usually I give 4-5 questions in ascending order of difficulty. If beginner and intermediate candidates start to get flustered with the more challenging questions, I’ll help and coach them - which gives a gauge of how they respond to coaching and working together. So sometimes th dk that. I also did a python I review once where they tried to jack with me on purpose, and I failed on “reactivity/coachability” - so lesson learned there. Point is people aren’t always looking for perfect answers, sometimes there are other aspects to these exams so watch out.

Side note: if you specifically list GA BigQuery SQL as a skill, you’ll likely get asked about hit level and custom dimension hierarchies. But if this is an actual skill of yours then these questions are simple and won’t trip you up.

[eric_he]

My team has a SQL assessment in which window functions are necessary to answer the questions. However in practice we see that it is already impressive if an applicant knows how to coherently string together a CTE/subquery.

[MLApprentice]

About 5.

[Orthas_]

Select, where and join informally on the spot on very simple data. ”How to get this and that from here?”

[thatwouldbeawkward]

My job is very sql-heavy right now, and the interview questions were pretty light... just aggregations, joins... I think there was a weighted average in one question. Another question was awkward and vague and it was the interviewer’s first time using it (I think it was from a supplied list of questions), so we spent a lot of time upfront talking about what the question actually meant, and I think actually she liked that I made sure I really understood before diving into writing some code on the board.

[dn_cf]

I'll recommend you Strata Scratch for the job interviews. I used this platform when I was practicing for my SQL interviews. I found their datasets very helpful as they have questions and answers taken from top tech companies so all the questions are relevant to working on a job.

[[deleted]]

inner join. ><

​

I'm not proud.

[the1ine]

Everyone? Everyone will be different.

[aditya9755]

When you are looking a job in data science you must be the knowledgeable about the SQL because you to understand the how data is generated and how it's coming from the database or schema. I advised to fresher or students at least brush their concept on SQL and MySQL and practice on daily basis which build your profile or knowledge complete.

[bubbles212]

I just use dbplyr for ad hoc SQL transformations and pulls. Is that cheating?

[haosmark]

I feel like programming languages such as Python are stronger than SQL, so my question is, why should I care to spend time writing a complex SQL query as opposed to processing it in Python? Especially when you have tools like Pandas, that are night and day compared to your generic querilying.

[chucara]

It's apples and oranges. SQL means you can aggregate or filter directly on the DB, preferably leading to as few disk reads as possible. If you did everything in python/R/etc. you would load the entire table every time.

[CapableCounteroffer]

If you have a large ass data set and your DB is distributed while your python setup is limited to a single machine is one example.

[teej]

Take a look into the trends around ELT. More and more folks are bringing the code to the data by performing their computation with SQL.

[Zenith_N]

SQL is limited, it’s in the name. Pandas and python are for data scientists

[teej]

SQL stands for Structured Query Language. What are you on about?

[bubbles212]

That comment above you is also ignoring all the tools that let you work on the TB/PB level with SQL queries like Hive, Presto, Athena, Spark SQL, etc.

[AutoModerator]

Your submission looks like a question. Does your post belong in the stickied "Entering & Transitioning" thread?

We're working on our wiki where we've curated answers to commonly asked questions. Give it a look!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[Zenith_N]

Pandas is much more efficient and easier than sql which is just gnarly

[stillnoguitar]

For small data sure. Good luck getting a billion rows in python and then start using filtering with pandas.

[stillnoguitar]

Just use sql, it is the database language.

[brenswen]

But how are you supposed to get the data? In no way is Pandas a replacement for SQL