19 ms·
Medium-hard SQL interview questions
- thomzi12 6y agoHey, HN! Since I couldn't find a good resource online for the self-join and window function SQL questions I've encountered over the years in interviews, I made my own study guide which I'm now sharing publicly as a Quip doc. Would love your feedback or thoughts!
- mobileexpert 6y agoLeetcode has some SQL problems that I’ve found useful in interview prep: https://leetcode.com/problemset/database/ https://leetcode.com/problemset/database/
- thomzi12 6y agoGreat resource! I have personally found the top Leetcode SQL questions to be hit-or-miss without curation (too easy, too hard, doesn't cover topics like window functions / self-joins like I would encounter in interviews, etc.) but you're right, it is a great, interactive interview resource
- bogomipz 6y agoI enjoyed reading this. My feedback is that although a few of the problems had links to blog posts that provided some analysis of the problem and solution most of these did not. I think if you added a short analysis or explanation of what makes the problem tricky and the thinking behind the solution it would be very beneficial.
- beckingz 6y agoFor cumulative averages why not use window functions?
- thomzi12 6y agoGood catch -- I can add that in as an alternate solution. Thanks!
- beckingz 6y agoOf course! Now the thing to do is for me to recreate your list on github.
- thomzi12 6y agoYes -- making this a proper webpage with an embedded SQL editor like selectstarsql.com does would be a good next step
- beckingz 6y agoAre there any good ways to embed SQL in markdown? Like JupyterLab notebooks?
- cbcoutinho 6y agoYes, Azure Data Studio allows you to embed SQL in notebooks right out of the box: https://docs.microsoft.com/en-us/sql/azure-data-studio/notebooks-guidance?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/azure-data-studio/noteb...
- beckingz 6y agoThanks! I'll have to check that out.
- deleted 6y ago[deleted]
- rollinDyno 6y agoHello, how come you chose to use quip over Google docs? I'd expect you to choose the latter since you work for Google.
- thomzi12 6y agoYeah, it's because (1) I started this doc over a year ago when I was still at Salesforce (which owns/employees use Quip) and (2) AFAIK Google Docs still doesn't have native code block support :/
- pwesner 6y agoGreat resource. Thanks for sharing. Do you have any more resource recomondations besides the ones in the article? Maybe a book?
- data4lyfe 6y agoWe try to surface the best SQL interview questions at Interview Query (https://www.interviewquery.com/ https://www.interviewquery.com/)
- Lightbody 6y agoI think these are great. But I think there should be some representation around locking / concurrency / deadlock topics. Those tend to be the hardest because you can’t clearly recreate the right/wrong answer in a local test environment. Speaking as a person who waited far too long in his career to fully appreciate these topics, I wish I had been pushed to learn them much earlier.
- jandrewrogers 6y agoBehaviors around locking and concurrency are not standard. Not only do they vary widely across RDBMS implementations, they also vary depending on how you configure a specific RDBMS implementation in many cases. Some databases use lock structures that automatically detect and resolve deadlocks, so from a user standpoint there are no deadlocks but deadlock resolution has visible side effects that are implementation defined.
- hobs 6y agoYeah but the functions and style used in these answers vary across implementations (date_trunc, etc.) I highly recommend the SQL Cookbook for newer SQL dev as a quick reference comparison to see how often this is the case for even trivial problems in any RDBMS. Also, for your language I wouldn't expect all mid tier SQL devs to be able to write a recursive CTE from memory (though its useful, you can just look it up again), but something like breaking apart a query plan on your platform of choice is way more important as that muscle allows you to tell if your CTE was crap or not.
- bigtechdataeng 6y agoI suspect more SQL is written to support analytics than online systems. Locking and concurrency are used in a specific type of application, namely oltp. I learned how much when I joined a big tech company. The devs don’t write sql unless processing logs in the warehouse. But everyone from PM to support to data science and marketing all write sql.
- deleted 6y ago[deleted]
- ollien 6y agoThis is neat, but it would be really helpful if these examples included some kind of sample output of what was expected.
- thomzi12 6y agoSome of them have sample output but yes, it would be helpful to extend this to all examples. Good feedback!
- toohotatopic 6y agoThen you can expect your candidates to exactly provide that answer.
- namdnay 6y agoVery interesting. I never really "got" declarative languages, I remember a very long time ago I was working with Oracle and you could see the "execution plan" for your SQL queries. I kept wondering "why can't I build my queries directly with this?" - it seems so much simpler to my brain than SQL itself.
- TurkTurkleton 6y agoYou can't write query plans directly because you would have to manually take into account a number of factors that the query planner considers for you automatically, such as size of the table, statistics on the distribution of values, whether there are indexes that could be used to speed the query, and so on. Some SQL dialects (like Microsoft's T-SQL) do give you some ability to influence the decisions the query planner makes, though, like forcing it to use specific indexes, or forcing it to use scans or seeks.
- marcosdumay 6y agoAnd then comes Oracle and insists on applying equality filters first, because, duh, they are fast, and never take any of those other details into consideration. Honestly, Postgres does it right - you can enforce your query plan to any level of detail you want.
- paulryanrogers 6y agoDoes PostgreSQL have plan hints now? I thought they were opposed to them for fear they become unmaintainable and hard to read.
- lfittl 6y agoIf you really want to, the pg_hint_plan extension can be used for this - though I would use it very sparingly, if at all: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html (available on some cloud providers as well)
- 6y ago
- ineedasername 6y agoI think I'd be able to do all of these in my daily work, probably not as efficiently, with minor references to syntax guides (I don't use window functions often enough). In an interview, presumably my logic would, hopefully, shine through minor issues of syntax. Where would that put me? Maybe "okay to decent" when dealing with "medium-hard" questions? I would fail utterly at DBA management SQL and stored procedures, my responsibilities skew towards data analysis.
- dmurray 6y agoYeah, I'd be something similar and also wouldn't consider myself a database expert. I think this question list is tilted towards "can you express this complicated abstract transformation in legal SQL" than the practical day to day considerations of building and working with databases.
- cryptozeus 6y agoIt would be helpful to show output result for each.
- thomzi12 6y agoYep, Ollien made a similar point. Currently some of the questions have expected output but not all.
- vasilakisfil 6y agoI always thought that I suck in SQL but if these are medium to hard then I am not that bad actually.
- taberiand 6y agoThese are easy enough to do in your head. Also I would do a lot of them differently. And I don't think the answer to #6 is valid (in T-SQL at least).
- chucky_z 6y agoSQL for Smarties is the book that has proven, without a doubt, I am bad at SQL. https://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp/0128007613 https://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/d... If you want to get undeniably good with SQL, this is the book.
- revscat 6y agoEvery time I see an article such as this it reminds me how much I deeply abhor SQL. It is an ugly language, closer in feel to COBOL than something that can at times approach elegance, like Ruby or Scala. With languages like those, you can loo at your work after you are done and be proud of it beyond its purely functional aspect. SQL never elicits a response beyond “the task is finished and it does what I want”, typically with a “finally” in there somewhere.
- gigatexal 6y agoWhere you see ugliness I see beauty. I guess it is just what clicks for someone and what doesn’t. But together our strengths make for one really good programmer hence why I like working on teams.
- fiddlerwoaroof 6y agoYeah, my experience is basically the opposite to the GP's as well: for expressing the projection of data I want to work with, there are very few syntaxes I find more elegant than an SQL query. (I have similar thoughts about CSS)
- Scene_Cast2 6y agoWith SQL as an analysis / insights tool (as opposed to prod or dashboard use), answering one (nontrivial) question leads to follow-up questions that need a lot of query restructuring.
- gigatexal 6y agoWhen attacking a problem like that I find many queries that might build upon them etc., helpful than one single query to build a report
- codeulike 6y agoSQL isn't really code though, its more like a specification of what you want. Working out how to actually get what you want is then (usually) the engine's problem. And when you're describing precisely what you want from large datasets with lots of columns, its always going to look ugly. I'd wager that if you re-wrote those snippets as Ruby or Scala operating against something tabular like CSV files, with all the joins and aggregates and so on done in code, it would look uglier. * ok sometimes sql (thinking specifically of SELECT statements) gets code-y, e.g. with inline formulas. But generally its more on the specification side.
- iblaine 6y ago>After an interview in 2017 went poorly — mostly due to me floundering at the more difficult SQL questions they asked me Could be you dodged a bullet. A company with advanced interview questions may have some ugly SQL code. For jobs that lean heavily on SQL, I expect candidates to know things like windowing & dynamic variables, in SQL & an ORM library. For SWE's, I feel basic SQL is fine.
- redis_mlc 6y agoI agree. Using advanced SQL as a hiring signal would be silly in that it just discards a lot of programming candidates for little benefit. I'd be happy if they knew what EXPLAIN was, since that impacts production on a daily basis. Also, the OP is basically "fighting the last battle" again. Most interviews don't filter out candidates based on SQL. Source: DBA.
- jerglingu 6y agoYes! Window functions and decent demonstration of datasets with varying granularity on top of the basic join/aggregation assessment is all that is needed in the SQL for interviews. And if you're an engineer, attention to redefining the query plan through appropriate use of temp tables. Anything else is a poor use of precious interview time for both parties, in my opinion
- gtrubetskoy 6y agoOne problem with this article is the number of times the solution involves COUNT(DISTINCT). One of the best SQL interview questions is "Explain what is wrong with DISTINCT and how to work around it".
- 0az 6y agoWhat is wrong with DISTINCT?
- cultofmetatron 6y agoseriously.. I'm building out some functionality using plpgsql and have used it. This is going to be haunting my dreams
- wesd 6y agothere is possible performance hit [1]. Also, it could mean that the data granularity has not been modeled well if there are duplicate rows. https://sqlperformance.com/2017/01/t-sql-queries/surprises-assumptions-group-by-distinct https://sqlperformance.com/2017/01/t-sql-queries/surprises-a...
- marcosdumay 6y agoNearly every time, it's a symptom of bad data normalization. But every time, it interferes badly with any kind of locking (that's DBMS dependent, of course), and imposes a high performance penalty (on every DBMS).
- philshem 6y ago“Think before you DISTINCT”
- barbegal 6y agoDISTINCT generally requires the results to be sorted which has O(n^2) worst performance so it can have a big performance hit on a query. It is best to make your database structure such that queries only return distinct data. E.g. by disallowing duplicates
- mosburger 6y agoWorth noting that this isn't all ANSI-SQL... e.g. I'm pretty sure WITH is a Postgres thing?
- Minor49er 6y agoNot anymore. Other implementations, like MariaDB and SQLite, have adopted common table expressions. They're basically syntactic sugar for subqueries in most implementations, but they can make some queries much more readable
- greggyb 6y agoCTEs are implemented by most (all?) major RDBMS platforms and were introduced in the SQL:1999 standard revision. - SQL:1999 https://en.wikipedia.org/wiki/SQL:1999#Common_table_expressions_and_recursive_queries https://en.wikipedia.org/wiki/SQL:1999#Common_table_expressi... - SQL standardization https://en.wikipedia.org/wiki/SQL#Interoperability_and_standardization https://en.wikipedia.org/wiki/SQL#Interoperability_and_stand...
- combatentropy 6y agoThe WITH clause, otherwise known as Common Table Expressions, is in ANSI SQL99. Common table expressions are supported by all of the major databases: PostgreSQL, Microsoft, Oracle, MySQL, MariaDB, and SQLite. They are also in certain minor ones, like Teradata, DB2, Firebird, and HyperSQL. Recursive WITH clauses are especially useful.
- matwood 6y agoWorth noting that MySQL only got CTE's in 8.0 while something like MSSQL has had them since ~2005. The reason this is important is that something like AWS Aurora only supports up to MySQL 5.7, and thus no CTEs. :/
- maxlamb 6y agothe WITH clause is now in SQL Server and Oracle
- 6y ago
- gigatexal 6y agoYup those questions are a stretch for me too! Love that though. Where are the pivot and unpivot questions?
- thomzi12 6y agoHmm, haven't seen those a ton at work / in interviews! Would love to see some examples though if you have any. Mode's SQL tutorial uses SUM(CASE ...) and CROSS JOIN to mimic pivots: https://mode.com/sql-tutorial/sql-pivot-table/ https://mode.com/sql-tutorial/sql-pivot-table/
- petepete 6y agoI think they're specific to Oracle and SQL Server. It's a pity because they can be very useful. PostgreSQL can do it via the tablefunc extension and there's `\crosstabview` which is built into psql. https://www.postgresql.org/docs/current/tablefunc.html https://www.postgresql.org/docs/current/tablefunc.html https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-META-COMMANDS-CROSSTABVIEW https://www.postgresql.org/docs/12/app-psql.html#APP-PSQL-ME...
- ryanisnan 6y agoGreat article. I can't help but feel like SQL is a poor choice for some of this stuff, though. More often than not, I find it much easier to pull the raw data into memory, and use a higher level language to do these sorts of queries. I am all for knowing the intricacies of SQL, as the cost for not can be very high, but I'm curious for your opinion here.
- tfehring 6y agoI work in R regularly and do most of my more complex data manipulation in R whenever I can, but most of these examples are simple enough that I think it makes sense to keep them in SQL. The only ones that struck me as really weird to do in SQL were the cumulative cash flows and the "histograms"/binning one, though the ones requiring window functions might also be slightly easier in R than in SQL. Also, a lot of these would need to be done in SQL in practice since the data wouldn't fit in memory. Any solution that requires loading the table [login (user_id, timestamp)] into memory probably won't scale very well!
- nerdbaggy 6y agoI use to think that too. This is a good talk about how smart SQL query engines are. It is an hour though https://youtu.be/wTPGW1PNy_Y https://youtu.be/wTPGW1PNy_Y
- bdcravens 6y agoFor small datasets, pretty much any approach will work. Once you hit hundreds of millions of records (which isn't even that big of a dataset), SQL still performs well on pretty modest hardware.
- yen223 6y agoWhat do you mean by SQL? In my experience, once you're hitting hundreds of millions of records, implementation details of your database engine will start to matter. A database designed for transactional workloads like Postgres will start to choke on aggregate and window functions, often taking minutes to run instead of milliseconds. A columnar database like Redshift (which exposes a SQL interface) will breeze through it without a sweat.
- dang 6y agoWe've changed the URL from https://gstudent.quip.com/2gwZArKuWk7W https://gstudent.quip.com/2gwZArKuWk7W to what that redirects to.
- deepsun 6y agoChecked just the first two answers: 1. MoM Percent Change It's better to use windowing functions, I believe it should be faster than self-join. 2. It seems that the first solution is wrong -- it returns whether "a"-s parent is Root/Inner/Leaf, not "a" itself. I'd instead add a "has_children" column to the table, and then it would be clear. Second solution works, but without optimization it's 1 query per row due to nested query -- slow, but not mentioned.
- deepsun 6y agoAnswer 4 is very very bad, you're doing O(N^2) JOIN. It's not just slow, it will fail on bigger data. The question just screams for windowing functions, and cumsum is a canonical example for them. Sorry post author, you'd fail my interview :)
- thomzi12 6y agoThanks for the feedback! The first section is intentionally about self-joins since they get asked about in interviews, but other people have brought up that window functions are more efficient, so I'll add in those solutions as well. Sorry you feel that way! Thankfully my employer felt differently :)
- beckingz 6y agoIf it runs fast fast enough or the business can wait long enough, slow and ugly is acceptable.
- thomzi12 6y agoYes, in particular for data analysts using SQL to answer product/business questions in a one-off fashion
- MaxBarraclough 6y agoUnless the dataset grows, poorly optimised queries slow down superlinearly, and things start falling over.
- zozbot234 6y agoNo questions/examples featuring recursive CTE's? They tend to come up in anything involving queries over trees or graphs. They're also a relatively new feature where having some examples to show how they work may be quite helpful.
- magicalhippo 6y agoTook me some time to wrap my head around writing a recursive query. Also had to study the docs a while to get "merge into" to work right, for doing combined insert/update.
- hotsauceror 6y agoOne of the first tasks I give associate DBAs is writing a recursive CTE to get a blocking chain. It’s an interesting and useful exercise.
- xupybd 6y agoFor me the hardest part of the first question is understanding the acronyms. I think MoM is month on month. But MAU, no idea.
- thomzi12 6y agoHey! MAU = Monthly Active Users. Sorry about that, I think this could be described as data analyst/analytics jargon. Good feedback to spell these out, thanks!
- ziftface 6y agoI agree, it almost seems they were put there to add artificial noise to the problem. It doesn't seem like you're selecting for the right skills here.
- thomzi12 6y agoSorry about that -- in real life you would just ask the interviewer what MAU meant if they didn't spell it out. The purpose was to make the questions more realistic, since at least in my experience in data analyst interviews the questions are asked in the context of actual business or product situations ... like company leaders, PMs, or others wanting to understand trends in MAU.
- hotsauceror 6y agoRDBMS platforms without function indexes means that some of these queries will force a row-by-row execution over your entire table. Enjoy running SELECT ... FROM a INNER JOIN b WHERE DATEPART(a.date, month) = b.whatever on a table with 500 million rows in it.
- paulryanrogers 6y agoThankfully even MySQL has them, at least as of version 8
- hotsauceror 6y agoYeah, I’m jealous of all the postgresql and MySQL people. SQL Server doesn’t have them and this kind of query would cause pain.
- branko_d 6y agoSQL Server can index a computed field, which is more-less the same thing.
- matwood 6y agoAs someone who used to use MSSQL extensively and is now forced into MySQL, trust me, do not be jealous of the MySQL people. 5.7 fixed some of the most egregious issues and 8.0 is adding some features, but the smoldering embers of the dumpster fire are still around.
- minimaxir 6y agoSQL 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 6y 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 6y 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 6y 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 6y 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.)
- kulig 6y agoWhy are experienced people still jumping through so many hoops to get a damn job?
- ridaj 6y agoThe first few answers are unidiomatic where I work. Analytical functions would be vastly preferable to self joins, especially in the case of the join with an inequality that is proposed to compute running totals, which I assume would have horrible performance on large datasets
- userbinator 6y agoA bit off-topic, but this is another one of those sites that show nothing without JS, and looking at the source reveals something a little more amusing than usual: content that's full of nullness.
- mrspeaker 6y agoAt least they do it with a bit of flair though: the thin blue "loading" bar works without JS!
- pier25 6y agoOff topic but... anyone knows what they used for the rich text editor? It uses React but I imagine there is some other library like ProseMirror here.
- savanpatel 6y agoThe solution for the tree question is wrong. Consider the tree (1 2), (2 5), (3 5), (4 3), (8 1), (5 6), (6 NULL) It gives the wrong answer.
- sk5t 6y agoI'd ding the over-use of CTEs, when subselects are often more appropriate and better-performing. Kind of a "every problem a nail" thing going on here.
- combatentropy 6y agoDo you mean subselects in the SELECT clause or in the FROM? Usually subselects in the SELECT clause have been the source of dogslowness. Reorganizing them into the FROM clause sped up queries 1,000 times. And I thought CTEs were just a way of moving subselects from the midst of the FROM clause to the top of the statement, where they're easier to understand.
- sk5t 6y agoIn the FROM clause -- although I understand future versions of pgsql will address this, the "WITH foo AS ... " approach can be horribly wasteful, materializing all the results, whereas "SELECT * FROM ( SELECT f(x) AS a FROM baz ) qq limit 1" lets the optimizer do its thing.
- meritt 6y agoMost people have no idea what an optimization fence is and opt for CTEs because they yield "cleaner" queries, despite nearly always killing performance. I've always found explicit temporary tables, where I can add indexes, are often a great solution for performance and readability.
- matwood 6y agoI mostly agree. I'd start with the simplest possible query and move from there. I enjoy SQL, but once I start querying large datasets or doing anything remotely complex, the 'right' solution is the one that is fastest. And fast always depends. Typically I have to write up a solution a few different ways, test and tweak.
- eyelidlessness 6y agoAt least on recent versions of Postgres, CTEs are often equal in performance to subqueries (if they're side-effect free and non-recursive they get inlined).
- oyoun 6y agoI think SQL is a language to be known by every programmer. With the right query, you may solve a problem that may take 100 lines in other languages. It is so usefull, reliable and does not change every year.
- TrackerFF 6y agoWith the extreme popularity of pandas, I think a lot of Python programmers would be amazed how clean and easy-to-read SQL queries look like, compared to the (downright) mess that's being written to query pandas dataframes.
- bradleyjg 6y agoDepends on the transformation. Dropping a couple of columns in a wide dataset is extraordinarily ugly in sql vs anything else, for example.
- beckingz 6y agoJust don't pick them in the first place? Though I'll grant you that extremely wide data is a lengthy experience to write SQL for.
- zbentley 6y ago...how is it ugly in SQL? Don't SELECT those columns. Or, if you need to do multiple things with the dataset sans those columns, use a CTE of a query that doesn't SELECT those columns. Or a view. It seems like SQL is only uglier if you're used to "SELECT *"ing, which is asking for trouble--in SQL or when doing the equivalent with other row/column data manipulation tools.
- bradleyjg 6y agoI don’t see what the issue is with using something like spark’s drop over using select.
- thomzi12 6y ago
- arh68 6y agoFor the second one, it seems most natural to reach for exists, or something (I have not tried this code..) select node , (case when parent is null then 'Root' when exists ( select * from tree c where c.parent = node ) then 'Inner' else 'Leaf' end) "label" from tree EDIT: also, in the fourth, it seems like you'd want to partition the window function, who cares about order. Something like sum(cash_flow) over (partition by date) "cumulative_cf"
- combatentropy 6y agoA subquery in the from-clause is usually lighter than the select-clause. I just so happen to have a table with this structure and hundreds of thousands of rows. This way runs in half the time. It is surprising, in fact, that the difference isn't wider, because Postgres's explain-command says it costs 1/100 as much. select t.node, case when t.parent is null then 'Root' when p.parent is null then 'Leaf' else 'Inner' end as label from tree t left join (select distinct parent from tree) p on t.node = p.parent
- danbmil99 6y agoThis is idiotic. Why in the world would testing for rote memorization of something anyone can look up easily be a reasonable filter for talent and experience in a programming role? A friend of mine did numerous interviews at a large company, hours out of his time and those of the interviewers, only to be caught up by some inane SQL question asked by a know-nothing after the entire process of interviews had been completed. Why not ask about obscure regex expressions? Better yet, how about baseball scores? Hair bands from the 80s? It's time for the valley to get real about how to judge the merit of applicants. The present state of affairs in tech recruiting is a joke.
- kmonsen 6y agoThat’s because evaluating talent is really hard, and this is the best proxy we have. The dirty truth is that promotion’s is usually even more arbitrary/political. I’ve seen extremely talented people be denied deserved promotions multiple times and people so bad I have no idea why they have a job get promotions. It’s more about who you know than what you bring to the table.
- deleted 6y ago[deleted]
- m12k 6y agoIt isn't the best proxy though, because it's not even trying to simulate the conditions under which real work is done (e.g. in real work, most developers will often look up details, and it's no problem at all - what matters is how they think about solving problems, that they know what they don't know, and that they fit into the team). This is a proxy for 'is this an experienced person with a strong memory?', not 'is this a qualified candidate who would perform well in the position?'. It's quite possible to fit the latter and not the former. If you're the size of e.g. Google so your problem is more to weed out bad candidates quickly rather than catch all good candidates, then sure, go ahead (false negatives are acceptable to completely avoid false positives). If you are a smaller company, then your pool of applicants is quite limited and it's much more important that you don't pass over any qualified candidates that happen to fall into your net.
- S_A_P 6y agoI flip back and forth between deep diving in (my case) SQL Server skills and .NET Manipulation. In the world I live, it makes the most sense to do set based manipulation in SQL and logical entity based logic in C#. I work in a unique enterprise niche that has about 4 options based one either java or .net. Sql knowledge definitely gives you a leg up for complex reporting, and there are cases where I love being able to debug super quickly when comparing inputs to outputs. However, when I run into a SQL script that is 5000+ lines long and have to debug it, I much prefer the .NET side of the fence. Should someone ever come up with a bridge that gives you .NET level of visibility into the active datasets in a SQL query I would pay them 4 figures without question...
- yellowapple 6y agoI take it you've already looked into CLR stored procedures? I haven't used 'em in a whole lot of depth, but they do seem to at least give some of the tools for a "best of both worlds" approach.
- beckingz 6y ago5000 + line SQL scripts???? What would one of these do?
- S_A_P 6y agoLegacy code from Powerbuilder days. But it does things like calculate month over month inventory, Mark to Market value, Risk, or things related to Energy and commodities trading.
- vsareto 6y agoThe ones I've seen handle tons and tons of edge cases or do extra validation. The SQL programming languages are pretty verbose in terms of syntax.
- ojr 6y agoI’ve done my fair share of complex sql queries and complex data migrations, asking about JOIN during a random interview is unfair unless documentation.
- cameronh90 6y agoOut of curiosity: what is the use case for SQL window functions in application programming? Unlike most SQL, it doesn’t seem to reduce the order of the data coming back from the server, nor do anything especially faster than can be done on the client - and has the disadvantage of extra load on the database (which is harder to scale). Is it only useful for ad hoc/analytical queries, or am I missing something?
- dntbnmpls 6y agoIt allows you to "group by/aggregate/etc" on a row by row basis rather than once over the entire table. So whle GROUP BY defines a set to aggregate over the entire table ( and hence you get 1 row back ), window functions creates a criteria by which each row gets a set ( aka window ) to aggregate over for that particular row. In addition, you can define a "frame" over that "window" for even more refined aggregation. I suppose window functions are used more in the OLAP space than OLTP, but it is a very useful part of SQL. In my opinion, windows function is the most impressive and important part of the language. People can define, describe and provide examples but with SQL, it won't sink in until you try it out yourself and have the "Ah ha" moment. If you have a database server and some test data, try writing a few window function queries or try some online examples.
- kthejoker2 6y agoWhy do you think it's not "especially faster" than doing it on the client? The data is already sorted, partitioned, in memory .. adding a moving average calc or max value per category is certainly faster than fetching the set to disk, recreating it in an intermediate structure and then calculating the new value with all that partitioning, sorting, etc. to be redone client side. You can certainly use them in app dev for things like: * figuring out how many things are ahead of you in a queue * snapshotting (what's changed since the last time you were here) * comparing something to some other sample for outliers or inconsistencies These may be quasi analytical still, but ultimately they can manifest as properties of an object model like any other property to be developed against.
- cameronh90 6y ago
- fnord77 6y agoI think for a lot of people, SQL is a skill that doesn't stick. You learn enough to do the queries you need for your project, they work then you forget about them as you work on the rest of your project. These skills are perishable. Left outer join? Yeah, I knew what that was some time ago, but not anymore The days of dedicated SQL programers are mostly gone.
- deleted 6y ago[deleted]
- xeromal 6y agoI'm sure data scientists and such probably use SQL at least somewhat regularly.
- giantDinosaur 6y agoI used to think this was true, back in the days when I used 'Group by' mindlessly and fiddled with the syntax dumbly until I got the right answer. Once you spend some time thinking about the concepts of it all you should do much better at remembering and if you totally forget the SQL syntax that doesn't really matter (Modulo the hideously annoying random differences between the databases themselves, but that's true of anything.)
- doppel 6y agoI really don't believe that to be true. For our application(s) at work, the database is always the hardest part to scale and tehrefore also the biggest performance bottleneck, and the difference between good and bad SQL can be magnitudes of order of performance impact (on the query itself and locking up the DB for other queries). Only in a "Every database has 2 TB of memory and 64 cores"-world is SQL (and database design) a negligible skill.
- jleach82 6y agoI completely disagree. A good full-stack dev knows SQL pretty well: at least well enough to be able to work through the general questions in the document. Good SQL skills are essential, and the lack of good SQL developers I would attribute mostly to ORMs that abstract it away so mediocre developers can mostly ignore it (which makes the job a whole lot harder for people that actually DO know how a relational database works, and give it the respect it deserves)
- anonfunction 6y agoThe first solution for MAU has the wrong sign for the percentage change column: Previous MAU 1000 Current MAU 2000 Percent Change -100
- thomzi12 6y agoThanks, anonfunction! I've corrected it
- sadhana1234 6y agogoood one
- snidane 6y agoSQL is better than 99% of the nosql alternatives out there. But one thing it falls apart are these time series data processing tasks. It's because of its model of unordered sets (or multisets to be more precise, but still undordered). When you look at majority of those queries and other real life queries they always involve the dimension of time. Well - then that means we have a natural order of the data - why not use an array data structure instead of this unordered bag and throw the sort order out of the window. SQL realized this and bolted window functions on top of the original relational model. But you still feel its inadequacy when trying to do simple things such as (x join x on x.t=x.t-1) or even the infamous asof joins where you do (x join y on x.t <= y.t). In array databases with implicit time order both examples are a trivial linear zip operation on the the sorted tables. In traditional set oriented SQL it results in horrible O(n^2) cross join with a filter or in the better case od equijoin in sort merge join which still has to do two expensive sorts on my massive time series tables and then perform the same trivival linear zip that the array processor would do. Which is also completely useless on unbounded streams. Also many stackoverflow answers suggest to use binary blobs for time series data and process them locally in your favorite programming language - which points at wrong conceptual model of SQL engines. Is SQL really so inadequate for real life data problems or have I been living under a rock and Kdb is no longer the only option to do these trivial time oriented tasks?
- 3dbrows 6y agoHave you seen TimescaleDB? https://www.timescale.com/ https://www.timescale.com/
- snidane 6y agoI've seen it. Most of these 'time series databases' are for processing of structured logs and metrics to be plugged into you favorite system for monitoring site latency. Asof join is still an open issue in their issue tracker so it is not usable as a time series database. https://github.com/timescale/timescaledb/issues/271 https://github.com/timescale/timescaledb/issues/271
- 6y ago
- deleted 6y ago[deleted]
- nogabebop23 6y agoThe problem with hard SQL problems is that they are often one of two camps: 1. use some underlying implementation detail of the particular RDBMS or proprietary extension to the standard 2. are essentially tricks, like the typical ridiculous interview problem designed to "make you think outside the box". Yes, you can do almost anything in SQL but often you should not. I get the perspective here is data analysis where you probably need to know more SQL than the standard developer, but I still feel you should be testing for solid understanding of basics, understanding of the relational algebra concepts being used and awareness of some typical gotchas or bad smells. That's it. They'll be able to google the precise syntax for that tricky by date query when you're not guaranteed to have data for every month or whatever on-demand.
- nurettin 6y agoThis is more like "we want to make sure you can use recursive CTE" questions. To add some variety to medium-"hard" SQL questions you could add some lateral joins, window functions (especially lag if you want to get creative) and compound logic statements in where clauses.
- beckingz 6y agoFor creating a table of dates, what are our thoughts on: select * from (select adddate('1970-01-01',t4.i10000 + t3.i1000 + t2.i100 + t1.i10 + t0.i) selected_date from (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3, (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v where selected_date between '2016-01-01' and now() This works in MySQL / MariaDB.
- klysm 6y agoOh yeah that’s super readable
- dzonga 6y agosome of the problems with SQL, is it was written to solve problems when hardware was expensive. BCF, n all the normal forms etc. when doing analytics you want a flat table that's it. & when working with a flat table for analytics they're other tools better for analysis than sql e.g pandas. or sql like language used by column databases. once you've a flat table, you no longer have to do joins etc.