4 ms·
Answer 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 cu
by deepsun 7y ago
Answer 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.