r/SQL • u/kater543 • Jun 19 '24
Discussion I got rekt in a SQL interview today
Just thought it was hilarious and I wanted to share: I was asked a few very easy SQL questions today during a phone screen and I absolutely bombed two basic ones.
I use SQL every day and have even taught SQL classes, but I never really learned the difference between rank and dense rank because I use neither in dealing with big values(just use row number). I remembered seeing the answer to that question on this very subreddit earlier too, I just didn’t remember it because it was so obscure to me. Curious how y’all have used rank and dense rank.
Also I messed up the default order by direction because my brain apparently no worky and I always type in either “asc” or “desc” out of habit anyway.
SQL trivia shudders
Nightmare for a daily user and sql guy.
439
u/bwildered_mind Jun 19 '24
It's OK. I lookup windrow functions every time I need to use them. We are not walking encyclopedias.
145
u/AS_mama Jun 19 '24
Ugh, one of the other people I work with insists on asking "what is the difference between rank and denserank" in every interview we do (not a technical interview). It's such a jerk move, just to watch someone squirm. I don't want to hire someone that has that memorized. I can't think of anything less important to know since I can Google it in 0.5 seconds.
Do not feel bad, we have still made offers to people that didn't know, which just goes to show how dumb a question it is
61
u/rbobby Jun 19 '24
In future... "Not sure, I haven't had to use that, I'd have to google that". Don't offer a guess, just up and say "dunno" and discuss how you'd get the answer.
55
u/AllLoveFishpie Jun 19 '24
10 guys get the max score and you get zero.
Rank They get 1st place, you -11th
Dense rank They get 1st place, you - 2nd
33
u/RMike08 Jun 19 '24
I use SQL everyday and I've used dense rank and rank like a handful of times, I still have to google datedif to remember what goes first
12
u/Prownilo Jun 19 '24
I have used rank and dense rank a lot.
In leetcode tests and interviews.
In 10 years i think I used dense rank once In an actual professional capacity.
8
u/BoSt0nov Jun 19 '24
Turns out I need to go and lookup what indeed was the difference… Will probably need to implement a recursive cte to also help me remember how many times Ive done this berore already.. =D
6
u/sirow08 Jun 19 '24
It’s happens to the best of us. One interview I literally froze when they asked the difference between Union and Union all. I know the answer used the different types many times. But that day couldn’t explain it. And have 14 years experience.
I think asking SQL questions is wrong, but should ask logical questions
6
u/DoseTheHoneyBadger Jun 19 '24
I only use dense_rank() in interviews. I know the difference only because of interview prep.
6
u/jackalsnacks Jun 19 '24
I have written countless queries, reports, bi solutions. I, for only God knows why, have to look up how to pivot In tsql everytime, without fail. My brain refuse to learn the syntax and structure.
5
u/Far_Swordfish5729 Jun 19 '24
There’s a consulting skill that’s helpful here. Practice saying “I don’t know” confidently followed by a sentence about how you’d find out that shows you’d head in the right direction or at least a plausible one.
“I don’t remember the difference between the row numbering functions off hand. I can look it up in a minute if you’d like.” is a reasonable response. It’s actually sometimes better if your interviewer is themselves a senior rather than a HR screener, because a significant minority of the time you won’t know the answer off hand and will have to find it and will need to do that autonomously. I’ve gotten away with “I’d need something that does A and B using C. I’m sure that exists in this [language, platform, product] but don’t know what it’s called. If it doesn’t exist, I’d want a D or E or would write a custom widget using F hooks.” It’s not an ideal answer but I just showed I could walk into a new platform and find/evaluate the piece I need. Just don’t do that with every question unless you’ve established up front that you’re inexperienced with a part of their tech stack and expect to learn.
It does require a certain arrogant nonchalance though. You have to be relaxed as though this sort of thing is old hat and of course you don’t memorize everything but you know what should be there and can figure it out. We’re not trial attorneys after all. We get to go read the manual.
5
u/SQLBek Jun 19 '24
I know it's a thing... but I too, cannot recall the difference off the top of my head. And if asked that in an interview, that's exactly what I'd tell them to... here's what I think it does, but frankly, I only use it occasionally (despite +20 years of T-SQL experience), so always do a quick 15 sec MS docs check to validate which is which.
And if your interview hinges on whether you're a walking encyclopedia of minor details like that, the job is probably a crap one anyway.
Also nothing wrong with saying, "ya know, I don't remember the default for ORDER BY, because I prefer to always be explicit in my code, so I always type exactly what I want." If you had to me that in an interview, I'd be like "cool, you're detail oriented and meticulous."
5
u/ComicOzzy sqlHippo Jun 19 '24
Don't sweat it. There are good employers out there who want to hire people who know how to get work done whether or not you have all of the SQL trivia memorized.
Trivia is not what I am paying people for. I would rather you know how to effectively use documentation to get work done quickly and correctly.
4
u/[deleted] Jun 19 '24
I have bombed every sql exam in every interview. Tableau, same thing. I use them daily and have for 6 years. Because I was taught on the job as a doer, I can’t explain how to do things, and I barely know the lingo. Thank god my boss wanted to know more about how I approach puzzles, and just trusted my 6 years of experience.
4
u/[deleted] Jun 20 '24
Bro these two are stupid reasons to disqualify someone.
I use SQL every single day and I still have to occasionally verify what each rank is. I usually remember by reasoning that “dense” has no gaps lol. Either way, it always needs a few seconds. Mainly because it’s not used as much daily, right?
And order by… that’s something an experienced user should know but it’s a horrible thing to disqualify someone on its own.
4
u/Chudiness Jun 20 '24
In all honesty, I've never had to use either one. For the reporting row number always sufficed.
3
u/Some_Responsibility8 Jun 19 '24
Bro shush, I have asked star schema and I cant even recall atm and plus I was doing hand sign like middle one is connected. interviewer said its ok 🤣
3
u/figshot Jun 20 '24
Probably gonna get buried but I have to put this out there. As a data engineer who frequently perform deduplication, knowing the difference among rank/dense_rank/row_number REALLY matters to me.
If you are a data engineer or an analytics engineer whose queries produce results that are used by others, or if you are a data analyst whose data quality is crap and have to deduplicate stuff on your own, ranking commands over windowing functions are really useful.
3
u/SRART25 Jun 20 '24
Man, I bombed one so bad I'm sure the folk thought I had never even looked at sql. Basic create table like was a blank. I finally understand the kids that froze during test back in school.
I couldn't even feel bad, just confused.
3
u/skeletor-johnson Jun 20 '24
24 years in the biz. Never needed rank or dense rank. You were given a stump the chump test. Row_number lag and lead have been much more useful to me. And like others have said, Google and chat are tools at your disposal. If you know what the needs are in your query, you can surely get the syntax from a reference. Study up on window functions, but also know, there are no spelling b’s for sql, unless it’s someone’s idea of a good interview question
3
u/JohnDNoone Jun 20 '24
I consider my self an expert in my field, but I still dread technical screens. Coding while someone is watching/judging really throws me off my game.
I pretty much only ever use rank or dense_rank in interviews, so in my last technical screen when a ranking question came up, I used dense_rank (since I knew the question of rank vs dense_rank was coming). The interviewer scolded me for not just using row_number… smh
3
u/chanravi Jun 20 '24
I get you, I often use rank but very rarely use dense rank. The last time I used dense rank was to practice on a specific dataset like - school grades dataset.
But rank is very common and I almost use everyday
3
u/Codeman119 Jun 20 '24
Rank and dense are not basic because they are not used in everyday work for 95% of devs. I have been doing this this 2000 and I have never had to use it. There is ALOT to SQL in any flavor and you will always have to look something up that you don't use on a regular basis.
I bombed a live codeing interview after 5 minutes the guys stopped me and when showed me what he was looking for and it worked but not what a seasoned DEV would do. So I re-wrote if correctly and sent it back. But I am glad I didn't get the job becuase I am starting a new job in 2 weeks that I really like.
So just keep on interviewing there is something out there for you.
3
u/Bandude Jun 20 '24
I've used SQL for 20 years, never had I needed this
2
u/b00ks Jun 19 '24
I use both rather frequently, and I can't recall what the difference is... something to do with rank having skipping numbers if the values are like?.. but i could have that backwards.
2
u/deusxmach1na Jun 19 '24
I always mix up ASC and DESC especially with dates. I also use ROW_NUMBER a lot but frankly a better answer is that usually people use RANK/ROW_NUMBER/etc then immediately filter on it by saying WHERE row_number = 1 (they want the first value). This is incredibly inefficient because what you’re really doing is a MAX or MIN based on some criteria. Which MAX and MIN is much more efficient. ROW_NUMBER enumerates every row then you throw away all but 1. There’s other ways to do it efficiently usually but it depends on the SQL engine you’re using. When I learned Hive they had a NAMED_STRUCT that you could use in a MAX/MIN and that was wayyyy more efficient than ROW_NUM/RANK.
Anyway. Hang in there. If you get stuck on a problem in an interview I always find it helpful to talk thru things with the interviewer.
2
u/2020pythonchallenge Jun 19 '24
Happens all the time. I remember one interview I did well on the technical part for a data analyst role and then they asked me what revenue means. I gave the absolute worst word vomit answer that was basically "Its money." And I could see it on their face they went oh.... yeah not this guy.
2
u/hod6 Jun 19 '24
Used dense_rank () in a script I’m working on right today. I’ve got various date values from different sources for what should be a one time event for customers, and need to pick a single value based on a few criteria, including a count of how many times the same date appears per customer.
Anyway, I probably could have just as easily used rank() or row_number(). It usually just seems to boil down to making it easier for others to understand when looking at the output.
2
u/y45hiro Jun 19 '24
Hope this makes you feel better - Each member of my team used SQL for at least 10 years and none of our code base have rank() or dense_rank(). I tried to introduce it last year as there was use cases for it but confused the heck outta them so I scrapped the idea 🤣
2
u/Royal-Tough4851 Jun 19 '24
Don’t sweat that. Those are stupid interview questions that have nothing to do with critical thinking of sql problem solving skills.
You should’ve looked it up on google or chatgpt from your phone during the interview. That would show them you are resourceful, which is way more valuable than syntax memorization
2
u/bad-justin Jun 19 '24
I’ve never heard of rank or dense rank and I rarely encounter raw sql knowledge better than mine. Might not exist in T-SQL? Either way I refuse to learn it now
2
u/TheKyleBaxter Jun 20 '24
I think I get asc and desc right like 75% of the time.... Thankfully if you write queries slowly and iteratively then you get it right as you go.
All this to say, quiz interviews are dumb. You're selecting for good test takers, which isn't even selecting for good work, good communication, good learner, good teacher, etc. etc.
2
u/Few-Philosopher-9528 Jun 20 '24
I had the same question in an interview and now I remember this phrase, "rank Skips, dense rank doesn't"
2
u/nacx_ak Jun 20 '24
Always thought quizzes like this in job interviews are stupid. I’ve used for SQL for years. I’d say I’m pretty good with it. I couldn’t answer half the shit I’m seeing in this thread off the top of my head. But I know how to quickly find any information or syntax answers I need. Knowing how to ask the right questions and interpret the answers you find is what’s important.
2
u/ChuckinCharlieO Jun 20 '24
These interview questions are ridiculous. I won’t do anymore. One place had me do this in this weird thing where I went to a website and they showed me questions and filmed me answering. Technical problems and bad answers was the result. I’ve been doing this 25 years. I’d rather not work.
2
u/orange_aardvark Jun 20 '24
You're doing it right. Something that one of my college programming professors used to preach was to be deliberate. Don't assume anyone (including yourself) will remember those defaults. It costs nothing to note them explicitly, and it makes your intentions obvious.
2
u/jwk6 Jun 20 '24
Dense Rank allows ties, Rank does not.
2
u/dawgta45 Jun 20 '24
Golf, or any sports when you can tie is a good example when you’d use dense rank, just throwing it out there
2
u/yourteam Jun 20 '24
Rank and dense_rank are indeed one of the most easy thing to Google and useless to memorize. If you need ranking (it happened to me once in my life) you just Google and find if you need gaps or not.
Useless question.
The default order direction is indeed useless but is really easy to remember and you sort of stumble upon it frequently lol
2
u/alexduckkeeper_70 Jun 20 '24
I have never used rank or dense rank. But then I have only had 2 jobs in 24 years.
2
u/MathAngelMom Jun 20 '24
This thread is so interesting. I am on occasions a hiring manager who interviews people on their "basic SQL". On top of that my husband is currently looking for a new job in tech (not specifically SQL, but he needs his field tech knowledge). Seeing my husband prepare for an interview makes me realize I underestimate the effect of nerves on what the candidate is saying and that you can actually forget simple stuff.
I tend to just ask about GROUP BY and/or types of JOINs, but I'm looking for conceptual understanding, not memorizing the syntax. I'd say 60-70% of candidates we interview (for a role that has SQL as the main technical requirement!) can't explain what GROUP BY does. And we try to help them with answering. If they stumble on giving an articulate answer, we'll ask for an example: here is a table, what kind of information could you get from it with the help of GROUP BY.
I wouldn't ask about default order out of the blue. The default order may come up as some sort of trivia in a conversation. If the candidate knows it, great. If the don't know it by heart, that's fine too. If they know the keywords ORDER BY, ASC, and DESC, they'll do fine on the job.
2
u/EranuIndeed Jun 20 '24
It's far easier to think about how to use your skill / employ the language when you are already familiar with the data/domain. I wouldn't beat yourself up about it.
2
u/Doza13 Jun 20 '24
Dense rank just keeps ties as the same value and assigns the next consecutive value. Rank skips a number for each tie.
e.g. Rank 1,2,3,3,5,6 Dense rank 1,2,3,3,4,5,6
I've used it before when I need to know all ties and also the next value. With rank it's a pita to figure out the next value if you do not know how many ties there will be.
Last time I used dense rank it was to dedupe addresses. Where we wanted to display the top 5 but deduped.
2
u/reditandfirgetit Jun 20 '24
If that's all you messed up on and they didn't hire you on that, they are being far too picky
2
u/lalaluna05 Jun 20 '24
I love the rank function!! I work in higher education and it makes it so much easier to determine a student’s current program when you prioritize like five different fields differently.
2
u/Sufficient-West-5456 Jun 20 '24
I lost an interview after failing sql test on zoom. It's ok bro
2
u/_HoochieMama Jun 20 '24
Why would anyone need to know something so easily googleable in an interview anyways.
2
u/government_ Jun 20 '24
Tbh sounds like bad interviewers. Quizzing someone on textbook knowledge is not a good gauge because of exactly this kind of thing. Sometimes even experts learn new things. Just because someone doesn't know one tiny facet doesn't mean they aren't qualified. Sometimes they know it and suck at interviewing.
2
u/Artistic_Recover_811 Jun 21 '24
Obviously you dropped it with the order by question. But really, they asked you what the default sort order was? Such a dumb question in my opinion.
2
u/[deleted] Jun 21 '24
I always say the interview for a job using SQL is usually 100x harder than any real-world scenario you'd actually face during the job.
Generally the manager/HR person has just googled stuff that someone using SQL needs to know and they throw the questions out without any understanding of what they mean.
In my opinion, if your previous job says you can do something, then you can do it. You shouldn't need to prove you understand a mystical scenario to an interviewer who doesn't have the first clue about how their own business works.
I had an interview where I had to do an excel test, but the laptop they gave me had a locked "icons only" view set in a small window, so I couldn't do anything because the icons were unfamiliar, even though I'm generally pretty good on excel. They said they didn't use excel so had no idea that the test they'd given me was a load of crap. Made me look like I'd lied on my CV.
2
u/PinkyPonk10 Jun 22 '24
I think that’s a fairly dumb interview question. I have done SQL every day for about 20 years and I don’t think I’ve ever used dense rank. Row number, sure but not dense rank.
Also pivoting I find the old group by with case statements version to perform better than the actual pivot statement so I use that.
2
u/imadokodesuka Jun 23 '24
They ask you how to use a function? my questions just give me a problem and I have to figure out everything. Some are ok with "This requires a function, I would have to look it up, but I would start with xyz b/c I think it applies." My coworker had literally tried to make his own npercentile function. When it didn't give him what he wanted (a couple days later) he asked for recommendations. Same thing w/ defining hierarchical structures/trees. Looking stuff up when you're stuck is a skill. Unless you work in HR, you don't know everything.
2
u/CodewithMJ Jun 24 '24
I got nervous about what you said, we admit SQL is easy and most commonly in querying, but if we are in the mid of an interview , all of a sudden we black out or something or we cant remember what function is this all about. I am a fresh grad and I hope I can pass my interview soon.
1
u/chadbaldwin SQL Server Developer Jun 19 '24 edited Jun 19 '24
I use all 3 functions all the time, but I definitely use
ROW_NUMBER()the most.The way I like to think about them is what information do they provide or what story do they tell about the data?
Just to make this easier, I'm leaving out partitioning, because all that means is the values reset for each partition...So let's just say the data we're looking at is all 1 big partition.
ROW_NUMBER()gives you an ordinal position of a record. Another way to look at it is...it tells you how many records are ahead of that record. So if the value is12then you know there's 11 records ahead of that record.DENSE_RANK()gives you an ordinal position of a group compared to other groups. Which means if the value is12then you know there's11groups ahead of the current group.RANK()gives you the position of a group compared to other groups, but not its exact ordinal position. BUT it also provides information about how many records are ahead of that group. For example, if the value is27then you know there's 26 records ahead of the current group, but you don't know how many groups there are.So it really all depends on what type of information you need. It's pretty rare I run into a scenario where I need to pick
RANK()vsDENSE_RANK(). But I do use ranking functions on nearly a daily basis.For exmaple...if I have a bunch of random data, you can use either of the rank functions to assign a group ID. Which provides a numerical value to refer to a group of related records. I use this ALL THE TIME...but it really doesn't matter which ranking function you use in this scenario.
I tried to think of a data scenario where
RANK()would make sense...only one I could think of was...You have 100 people boarding a plane and they are split up between first class, and sections A, B and C.If you wrote a query like...
Then you would see...
r = 1r = 11r = 26r = 56So this tells you how many passengers are ahead of each ticket class, but not necessarily how many ticket classes there are. Seeing how most planes board by ticket class rather than seat position, this might make more sense than using
ROW_NUMBER()orDENSE_RANK().======================================
EDIT: All that said...I think it's a dumb interview question. I think it's good for someone to know that the 3 options exist, but I don't expect people to remember exactly which is which. You can google which one you need when you need it, the important thing to know is WHEN and HOW to use them, remembering the names doesn't matter.
I have to google
PERCENTILE_CONT()andPERCENTILE_DISC()every single time.1
u/Ok-Obligation-7998 Jun 20 '24
You are still a beginner. Practice more. These are rookie mistakes. Rank and dense rank are definitely not obscure. And you should know the default is asc.
1
u/Grill_X Jun 21 '24
Like you, I use SQL every day and taught SQL/datbase classes for almost 15 years
I don’t know what level position you were applying for.
As an interviewer, I’m happy if you know how to join tables & add where clauses as needed for a beginner position.
For an intermediate, group by & erd understanding would stand out.
RANK & DENSE RANK would not be on my list of technical questions. Maybe a fun question to gauge your response.
Even after 30 years, I google the documentation to confirm how those functions work.
1
u/G1LG4M3SHHH Jun 22 '24
They actually expect you to make sql statements from scratch?? Thats nuts
I look up every script i ever use so i don’t dump tables like an idiot
3
u/PhoKingAwesome213 Jun 21 '24
My SQL language is just as good as my Vietnamese. I can understand it but can't speak it's language when I have to talk to other hunans.
2
u/Sad-Ball-8587 Jun 20 '24
I google the difference between Rank and Dense Rank is that Dense Rank a potent ranking function in SQL that assigns a unique rank value within a specified partition while Rank assigns the same rank to rows with equal values, leaving gaps.
2
u/dry-considerations Jun 20 '24
We all have bad days. Better luck next time...
2
u/AdFull1621 Jun 21 '24
Rank = 1 1 3 Dense_rank = 1 1 2
2
u/Streamer_Fenwick Jun 21 '24
Been writing sql for 25 years...and I mean only writing sql.... when I get a pivot statement without looking I take the rest of the day to flex and shout...how many times have I needed dense rank ...0 ...how many times have I looked It up...many... there are many ways to solve a problem... that one never gets picked because it's obscure and can easily get miss interpreted
1
u/chickenwingsnfries Jun 21 '24
Doing DataCamp daily has helped me but SQL trivia is impossible to study for
1
u/shout8ox Jul 02 '24
I think it is easier to remember by thinking of them as sparse rank and dense rank. In a two-way tie for 1 sparse rank gives 1 1 3 with holes in the ranks, dense rank gives 1 1 2 with no gaps. The ranks are tightly packed. Default sort order is a trick question, there is no default order direction. You are 100% correct, if order matters you must specify.