Hacker News Reader

Comment by deepsun | Hacker News Reader

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 :)

Thanks 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 :)

If it runs fast fast enough or the business can wait long enough, slow and ugly is acceptable.

Yes, in particular for data analysts using SQL to answer product/business questions in a one-off fashion

Unless the dataset grows, poorly optimised queries slow down superlinearly, and things start falling over.

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

Would you really interview and hire for a job based on SQL tricks instead of spending $50 to give someone a reference book?

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

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

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

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

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

Reply on news.ycombinator.com