Often, multiple type of sql solutions exist for a problem. For example whether to use window functions or joins or cte, Any of these would give correct answer, but limitations would be sql versions or readability. How would you chose which solution is appropriate?
My latest sql interview was only hackerrank questions about substr... I was super disappointed as it didn’t really test sql. There wasn’t a single join even.
Which company? Was it a phone interview?onsite could be different.
It was onsite and they had technical difficulties setting it up that portion of the interview. Wasn’t exactly a tier 1 company though. Not going to name it as I’d be too traceable.
Following. I wonder this too. Also if interviewers care about query performance if multiple interviewees get the right answer
I choose whatever comes to my mind first. It also depends on the tables.
As you write up a solution, let your interviewer know that you could also use other techniques to solve it, and what the tradeoffs could be in terms of available indexes and the distribution of data. A good interviewer cares less about the specific solutions you come up with and more about your thought process and how comprehensive your understanding is.
Are these sql questions asked in a swe interview? I've never had one before
Not in swe, usually for analytics roles
Can someone help me to understand what’s the trade off or when to use window fun over self join or sub query? Can you guys share any link or doc related to it
For an interviewer doesn't really matter. Depending on the case e.g. Join vs Subquery vs CTR you can always check which of these is using the lowest resources and as such processing is faster if that is of importance.
Right. But, it is obvious that window functions are readable and easier when they can be used, I am not sure if interviewer will be happier with using those or without.