4 ms·
Checked 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 t
by deepsun 7y ago
Checked 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 7y 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 7y 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 7y agoIf it runs fast fast enough or the business can wait long enough, slow and ugly is acceptable.
- thomzi12 7y agoYes, in particular for data analysts using SQL to answer product/business questions in a one-off fashion
- MaxBarraclough 7y agoUnless the dataset grows, poorly optimised queries slow down superlinearly, and things start falling over.
- deepsun 6y agoThat's exactly what I look against on interviews. Slow SQL queries tend to work fine for some time, and as data grows, they start failing. I'd want to hire a professional, who knows the perfect way of doing that, so for them it would be as easy to make it right, as to make it ugly.
- lonelappde 7y agoWould you really interview and hire for a job based on SQL tricks instead of spending $50 to give someone a reference book?
- kragen 7y agoIf someone claims to be a SQL expert but hasn't bothered to read a comprehensive book cover to cover, it probably isn't because they didn't have $50, especially now that we have Library Genesis.
- WWLink 7y agoI've never claimed to be an expert in anything, but if I read a book on SQL from cover to cover, I'd probably still not remember wtf BETWEEN ROWS was unless I was using it daily. -- The guy that uses C and C++ all the time and often forgets the order of the arguments to common functions.
- yellowapple 7y agoHell, at least 50% of my day job involves hacking together SQL Server sprocs, most of which have thoroughly used (and abused) most or all of the concepts demonstrated in the article, and I take no shame in admitting that I'm frequently double-checking syntax or looking at how other people solved similar problems.
- eyelidlessness 7y agoI spend more and more of my time writing relatively complex SQL, and I too frequently look stuff up. That's what good engineers do, like all the time. I seldom leave my editor when it's TypeScript, because intellisense does most of the looking up for me. But for SQL I hit the google frequently and I'm not ashamed to admit that at all.
- kragen 7y agoI guess what I'm trying to say is that having a reference book won't help you if you don't know what to look for. Perhaps the interview problems can tell you who knows what to look for and who doesn't.
- heed 7y agoAlso for Q1 - what if there is gap one or more months? It seems we should first generate a month series based on the min and max months, and left join to the series to account for months with zero activity.
- thomzi12 7y agoSure! Practically speaking, as a data analyst, I would probably notice a missing month when plotting the trend of MAU over time. (You can make "but what if a month is missing?" a latter part of a multi-part interview question) Generally I would assume that data engineers would have a month of no users set to zero or that I could ask them why that's not the case and note that for future reference.
- meritt 7y ago> It's better to use windowing functions, I believe it should be faster than self-join. Your statement is making the assumption you have completely dense data and you can simply offset a number of rows to get the desired denominator. Sparse data is a very common occurrence, and now your MoM/YoY/XoX queries are completely incorrect.
- radiowave 7y agoGood point. I often find the easiest approach is to join against a sequence to 'unsparsify' the data, then use the window function. I'd guess this is likely still faster than a self-join, unless the data is very sparse.
- ScottWhigham 7y agoYep. Same here. I stopped reading after the second solution. I liked the questions though - just maybe the answers were too myopic.