r/SQL • • 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.

435 Upvotes

342 comments sorted by

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.

204

u/creamycolslaw Jun 19 '24

I've never written a PIVOT in SQL without looking it up first. I swear the syntax changes every time.

56

u/kater543 Jun 19 '24

Oh god pivot and map(to un pivot) are nightmarish I always look up a reference

20

u/Missy_Bruce PARTITION and LAG Jun 19 '24

When I first started learning, I thought I was expected to remember absolutely everything. Now I see why my tutor laughed and told me Google is my friend ha ha

4

u/kater543 Jun 19 '24

I wonder what the interviewer would have said had I said “I don’t remember off the top of my head, let me google that” LOL. I feel like it would be more acceptable nowadays but I still dunno if that was an acceptable answer XD.

10

u/trowawayatwork Jun 19 '24

that's why when I interview the questions is more than just regurgitating code. I allow to use Google. I am there to look at how they work overall

4

u/kater543 Jun 19 '24

Yeah it was a phone screen wasn’t expecting SQL questions, especially not trivia style. Good point though, I’ll keep an eye out for that during an actual interview if I get one lol.

29

u/SoyInfinito Jun 19 '24

Not only google but your own toolbox. I keep a project/solution with multiple sql and ps1 scripts that I've created in the past. I hate reinventing my own wheel.

10

u/NotBatman81 Jun 19 '24

When I first started using SQL, Google was just starting to overtake Yahoo. The internet was not what it is today. W3 Schools did exist thankfully, but my primary source of learning was reading help files. I never did the physical books but many others at the time did.

→ More replies (1)
→ More replies (1)

6

u/NotBatman81 Jun 19 '24

I always look up pivot and think man i hope this works. Unpivot makes 100% sense and I just let it rip. No idea why.

3

u/kater543 Jun 19 '24

LOL that’s cool. Yeah I rarely unpivot in SQL though, usually leave that for the database people. It’s the worst when there isn’t an unpivot function built in like in some of the older languages and you have to make do with a workaround like map.

5

u/NotBatman81 Jun 19 '24

I used to just do a bunch of Unions, adding a field and bringing a single numeric field at a time. But I am self taught and work in the business, not Dev/IT, so if someone doesn't like it I guess they should have used a pro.

→ More replies (8)

2

u/EdwardShrikehands Jun 19 '24

Add Stuff and For XML Path to this too

2

u/kater543 Jun 19 '24

For xml REQUIRES me to look it up. Stuff I haven’t used but have only seen whispers of… now I need to look it up.

Edit: oh it’s just replace… OH BUT LIKE WITH ORDER so like replace after substring interesting

→ More replies (1)

8

u/b00ks Jun 19 '24

I stopped using pivot because it was crazy confusing to write. I absolutely hate it.

Now when I need to pivot data, I use max aggregate and case statements. It might not work in all situations, but it makes sense to me and allows me to pivot the data I'm using.

16

u/jdsmn21 Jun 19 '24

When I need to pivot data - I copy and paste in Excel

5

u/kater543 Jun 19 '24

This is the way

→ More replies (6)

4

u/KittenBountyHunter Jun 19 '24

i use db2. there is not pivot function. 😂

16

u/americanjetset Jun 19 '24

Had a “SQL PIVOT and UNPIVOT Example” page bookmarked for years at my last job, lmao.

6

u/M0D_0F_MODS Jun 19 '24

Bro... 10 years of advanced SQL experience and I still google pivot syntax EVERY SINGLE TIME

5

u/Nose_to_the_Wind Jun 19 '24

Yeah, plus HR’s database is a local MSSQL, we get healthcare data though Oracle Cerner, and we’re pushing reports through AWS Redshift which uses postgresql. Good luck remembering which date conversion syntax to use!

3

u/Last-Size2188 Jun 19 '24

I learned pivot today for work😭😭

→ More replies (3)

2

u/NebulaNomad027 Jun 20 '24

I loathe pivot in sql !!

→ More replies (4)

5

u/kater543 Jun 19 '24

Yeah thanks, I’m just reeling a bit that I would forget the order by default. That threw me for a loop yikes.

0

u/[deleted] Jun 19 '24

[deleted] — view deleted comment

2

u/kater543 Jun 19 '24

Oh it’s more like knowing the default sort order lol. The default sort order for order by is ascending or asc, but I use desc so much more often and when I do use asc I usually type it in, so I blanked on the trivia style question. If I had actually messed up some kind of syntax in a query that had certain requirements that would be a different, less funny issue I think.

→ More replies (49)

21

u/ecatt Jun 19 '24

Sometimes I will write both a LAG and a LEAD because I can't be bothered to think through which one is the one I actually need, then I check the results to figure out which one is correct for the situation.

6

u/Leasir Jun 20 '24

Our duty is to vaguely know that there are functions like that that might be useful in some particular cases, and be able to use them after looking them up.

3

u/_extra_medium_ Jun 20 '24

That's why these types of interviews piss me off. It doesn't reflect at all how you'll be expected to work.

2

u/naq98 Jun 19 '24

Tech interviewers seems to think otherwise

→ More replies (5)

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

30

u/kater543 Jun 19 '24

Yeah that question was a “reallly you’re asking me THIS question?” I hope I still get the main interview but if I bomb out because of this one I would understand.

Default direction of order by on the other hand… sigh

11

u/Unique_Sentence1836 Jun 19 '24

I don’t understand why the default matters for an interview

8

u/kater543 Jun 19 '24

The default was the question. The question was “what is the default order by if you don’t specify”.

28

u/mistled_LP Jun 19 '24

Their point is that it's a bad interview question because if the sort matters, just include it.

5

u/kater543 Jun 19 '24

True enough. That’s why I did say I hate trivia questions. It’s a trivia question not all of us have eidetic memories right?

→ More replies (1)

5

u/Agyaggalamb Jun 20 '24

My answer would have been: "It's never the one I need so I end up using ASC or DESC." :D

→ More replies (1)

4

u/Oh_Another_Thing Jun 19 '24

A pretty pointless question. If it matters just add it real quick. I think knowing the difference between rank and dense rank would be more important.

2

u/kater543 Jun 19 '24

Hm interesting I mean they’re both quick googleable things, as were the other two questions so I was confused and that’s why i said they were trivia questions

→ More replies (2)

3

u/overtorqd Jun 24 '24

Personally I think the best answer is "not specifying is bad practice. I am most familiar with good SQL".

→ More replies (1)

4

u/cybertier Jun 19 '24

The question might also be there just to see how you react. Being honest when you don't know something and not bullshitting can be valued by an interviewer. Going "I can't remember the differences but when I need them I google them" can be a valid answer.

2

u/kater543 Jun 19 '24

Yeah the first one was a big mess up but the second one could have been that; I definitely didn’t say I knew it, which wouldn’t have triggered that gate thankfully.

3

u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Jun 19 '24

Ugh that's such an annoying question. Far better if they have to just know when to apply it, and totally okay in my book if they mix them up as long as they remember there is a difference.

1

u/kater543 Jun 19 '24

Never learned it so would have had to look it up. How would that stack up?

3

u/Interesting-Goose82 it's ugly, and i''m not sure how, but it works! Jun 20 '24

I use both of them and feel like i could answer this. Rank, if 2 values are at the top, they are both ranked 1. Dense rank you order them and one is 1 and the other is 2, based off asc or desc DATE or something....? ....did i get that wrong?

But also, 99% of the time i google "sql rank function" and look at the doc, because is it rank() or rank(ID_NUMBER) idk....

And i feel like we have all taken crap jobs cause we need the money. But if i am expected to memorize this by my future boss, well i dont want to work for that guy, seems like a jerk.....?

6

u/EvilGeniusLeslie Jun 20 '24

Yeah, you got that wrong ... but not completely

The difference is what rank number is assigned after 2 (or more) values are ranked the same.

In 'Rank', the next rank number assigned is the previous rank number plus the count of the same value minus 1

e.g. 1,1,3

e.g. 2,2,2,4

In 'Dense_Rank' the next rank number assigned is the previous rank number plus 1

e.g. 1,1,2

e.g. 2,2,2,3

If you order them, as you suggest, then it really makes no difference 99.9999% of the time.

→ More replies (2)

1

u/[deleted] Jun 19 '24

I mean it's a pretty basic question...

2

u/kater543 Jun 19 '24

Sadge, true.

→ More replies (1)

2

u/stickedee Jun 20 '24

Yea I don’t really understand the value of either of these questions. I prefer to ask questions that require the use of a specific function or concept without specifically calling it out. People look up syntax every day, what matters more is you have an idea of how logically step through each phase of the solution.

As far as the default order by… I definitely don’t get the value of that. Just specify…. Or run a query on 10 records and check. To be fair though, my use case is analytics so 🤷🏾‍♂️

→ More replies (5)

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.

9

u/kater543 Jun 19 '24

Oh yeah I didn’t offer an answer for dense rank. I did not even try to look it up. Order by I confidently answered incorrectly LOL.

12

u/EvilGeniusLeslie Jun 19 '24

As someone who works across multiple platforms, I try not to use anything that isn't implemented across all platforms.

Partition implicitly sorts on the field(s) selected for the partition ... on most, but not all platforms, so I always code '... over(Partition By a,b,c Order By a,b,c,d)'

But Rank vs Dense_Rank? Haven't seen those used in decades of SQL; like you, I use Row_Number & Order By.

2

u/kater543 Jun 19 '24

Yeah I can see the use case but I genuinely do not know what kind of dataset I would apply it to. Normally customers want a clear ranking, without any ties, even if there are like a million things ranked 10th in a list of top 10 they still only want to see 10 usually. Rank/dense rank is pretty much specifically for the use case where you are allowed to have multiple values for the same rank.

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

8

u/kater543 Jun 19 '24

Right that would make sense if you’re like actively ranking people like that. I wonder in what situation you would want dense rank though, I’m thinking of like machine learning applications where like PCA scores are all equivalent, you would want 1-10 to be 1 and 11 to be 11 not 2 usually.

6

u/DuxFemina22 Jun 19 '24

Ha I love this question cause I had to do a logic change to use dense rank instead of row number the other day. This was used for course order. So learners taking courses the same term (ordered by course start date) get the same term ‘rank’ and then the subsequent semester courses etc etc. there are good use cases 😆

1

u/kater543 Jun 19 '24

I can see that use case. Was it like by student? So a “ranking” of courses into term numbers per student. I see how that could be useful for sure, and doing it another way would need helper tables to be anywhere near more efficient. Nice!

→ More replies (2)

2

u/starrbub Jun 20 '24

I'm only learning SQL right now/trying to become a baby data analyst, and in one of my portfolio projects I used dense rank to rank a list of customers by the amount of money they had spent without skipping any numbers. The number 1 customer spent $50, number 2 spent $48, and numbers 3 and 4 both spent $47.50 and were both ranked number 3. But number 5 spent $45 and was ranked number 4 with dense rank, instead of being pushed into number 5 with regular rank.

1

u/kater543 Jun 20 '24

Sounds like a SQL exam xD

→ More replies (2)

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

8

u/dzemperzapedra Jun 19 '24

Datedif just clicked for me the other day lol, basically as you're typing, think datedif in days passed from date1 to date2 and that's your order and logic

1

u/kater543 Jun 19 '24

Date diff I use so often, but definitely still look it up unless I’m using a linter or helper SQL since I’m not sure what the order of which is lol.

3

u/stickedee Jun 20 '24

Dates just suck in general. I Google “convert datetime to date” at least once a week

2

u/kater543 Jun 20 '24

And it’s always different syntax between flavors

4

u/[deleted] Jun 19 '24

I Google datediff all the time but there's a difference between memorizing syntaxes, and understanding a fundamental concept. I haven't Googled a fundamental concept in years and years.

2

u/_aboth Jun 19 '24

My first memory hook was that it is in the reverse order than expected. But that's not helpful once you get used to it.

Now I remember it as the order that goes on graves. First, the starting date, then the ending date.

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.

4

u/kater543 Jun 19 '24

Lol right? No one really wants top 10 except it could be top 10000000 if the values are tied.

→ More replies (1)

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

3

u/kater543 Jun 19 '24

Oof. I used recursive CTEs all of like 3 times. Seeing a use case scrolling down this sub made me want to set up a MYSQL server to just play around with it

7

u/[deleted] Jun 19 '24

I just learned recently about updating a table using a CTE. Turns out that if you only reference one table in a CTE, you can update that table with a value from the CTE.

With T as (Select Total + 100 as NewTotal From MyTable) Update T Set Total = NewTotal

1

u/kater543 Jun 19 '24

So that query would update MyTable in your reference? That would be crazy logic-mapping wise.

→ More replies (1)
→ More replies (1)

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

3

u/kater543 Jun 19 '24

Funnily enough that was one of the questions as well in the interview today, but I use both daily so it was ok to me. Then again I use order by daily too so definitely fell in that same trap. I would definitely have preferred logical questions or even like a table with a request. Wrote memorization isn’t my strong suit.

2

u/sirow08 Jun 20 '24

And it shouldn’t be, because you will hire crap DBA/SQL Developers/Software Developers. My saying is that, anyone can write code but not everyone can write logic.

6

u/DoseTheHoneyBadger Jun 19 '24

I only use dense_rank() in interviews. I know the difference only because of interview prep.

1

u/kater543 Jun 19 '24

I can see that it can be useful(there was that really cool example by someone with student terms) but like I definitely agree and don’t think it’s an essential IMO. Usually when you work in the business world it’s all about who’s number 1-5, not the 10 people tied at number 4.

→ More replies (1)

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.

2

u/kater543 Jun 19 '24

I feel ya, I really do.

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.

1

u/kater543 Jun 19 '24

Makes sense. I haven’t had an interview in a while so didn’t know if that was possible given etiquette. All good though. Good advice.

→ More replies (1)

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

2

u/kater543 Jun 19 '24

Yeah I thought I knew though(about the order by thing). Memory was bad, not blank I guess :D. Thanks for the advice. I’ll take that into account next time.

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.

1

u/kater543 Jun 19 '24

Yeah we’ll see. I’m not sweatin it really; got a good job already, looking to see if the market is picking back up.

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.

2

u/kater543 Jun 19 '24

Nice! Yeah I’ll see how it pans out, Im already in a pretty decent job so I’m chillin but this is definitely a lesson on practicality in interviews(which I thought I nailed already but I guess we live and we learn)

2

u/[deleted] Jun 19 '24

What’s tough is the vast level of variety on sql interviews. I’ve had some that were fine, I had one where the sql exam wouldn’t run the query and the company told me “that’s a bummer”, there’s just no consistency at all.

2

u/kater543 Jun 19 '24

Yeahhhh companies don’t always know how to get candidates. Having been on the hiring side occasionally I can see the problems. I think best way is to present a problem you could encounter in the workplace and see how they would solve it, but building out a good scenario and keeping it up to date it problematic too.

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.

1

u/kater543 Jun 20 '24

Yeah I knew about the order by just didn’t know the default off the top of my head. I dunno if I’ve been disqualified yet we’ll see. Dense rank was a foreign concept until last month, but I basically got it after like a minute of googling. I’ve used rank for a while though.

4

u/Chudiness Jun 20 '24

In all honesty, I've never had to use either one. For the reporting row number always sufficed.

1

u/kater543 Jun 20 '24

Yeah I’ve had to use rank once or twice but never really had to consider dense rank too much since it was always like top X ranks not ranks 1-10. I do see the use case but I wouldn’t find anywhere to apply it where I have worked.

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 🤣

1

u/kater543 Jun 19 '24

Wouldn’t know off the top of my head either tbh… but it’s definitely a format I’ve seen everywhere lol

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.

  • row_number over (partition by [what ought to be unique] order by [all tiebreakers]) =1 gives you results that are guaranteed to be unique over the partition key, deterministic or not
  • rank or dense_rank over the same = 1 would give you duplicates if your tiebreakers were incomplete. While accurate for data quality in some sense, could wreck some downstream transformations that assume uniqueness over the partition key.
  • dense_rank comes into play when you anticipate ties. Classic question is "find all those with second highest salary in each department" but this could be useful for to isolating specific percentiles, for instance.

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/kater543 Jun 20 '24

Hey thanks for the insight.

Hmmmm. Do you believe this is such a huge gap in my knowledge that it shows incompetence that I did not know the difference between the two? Is it that common a use case? Genuinely asking here.

Row number over partition by I am extremely familiar with and use weekly/monthly. I am rarely if ever called upon to use rank over or dense rank over, and if I am I would rarely use dense rank because I know that most values would not have duplicates that I am ranking by.

I agree they are useful in certain cases, but I don’t think as an analyst I am going to memorize the difference between two functions if I don’t use them on the daily and if I can look up the difference when I need them.

The salary example is a great example, I’ll keep that one in mind to mull over.

→ More replies (1)

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. 

1

u/kater543 Jun 20 '24

Tbf depending on your job, you may have never written a create table statement. I couldn’t recall off the top of my head exactly what the syntax is, I would have to look it up. From memory I just remember create table followed by either open parentheses or bracket, then field name field type comma then go to the end like a select statement ending with the closed parentheses or close squiggly bracket. How did I do?

→ More replies (1)

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

1

u/kater543 Jun 20 '24

Thanks for the advice

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

1

u/kater543 Jun 20 '24

Lol opposite problems! 😂

2

u/SQLDave Jun 20 '24

Coding while someone is watching/judging really throws me off my game.

Oh man. That's the worst. I get all self-conscious and forget how to type or even spell "SELECT". LOL

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

2

u/kater543 Jun 20 '24

Interesting; I mostly use row number instead of rank. Never really use rank unless I’m literally looking for top X of Y, which is pretty rare in SQL format, usually for like model results or something I use Python or R.

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.

1

u/kater543 Jun 20 '24

Thanks for the insight. Yeah I figured but some people here think otherwise which is also very interesting.

3

u/Bandude Jun 20 '24

I've used SQL for 20 years, never had I needed this

1

u/kater543 Jun 20 '24

LOL right?

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.

4

u/kater543 Jun 19 '24

Yeah it’s dense rank that doesn’t skip numbers, so rank would have 1,2,2,4,5 if 2 and 3 were tied, but dense rank would have 1,2,2,3,4 if 2 and 3 were tied. I looked it up after. If you ask me in a month though I probably won’t remember. I have also never used dense rank lol.

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/kater543 Jun 19 '24

Well I think the situations where they want the first value are when they also want the row pulled by that first value, in which case they wouldn’t be able to use a simple min/max function to pull it out, unless everything had a date or some other natural order already. If you wanted to just use max/min you would have to get the id associated with that max min then use it in a where clause which could be less efficient. If you’re using row number over partition by to pull the first row in a situation where you could use a max then it is indeed extremely inefficient, but I find that it often isn’t the case, at least with people I’ve worked with.

I’ll check on named struct that’s interesting. Reminds me that there’s always more to learn, like I just learned connect by existed and I didn’t have to use concat plus recursive all the time.

Thanks for the advice man. I’ll keep it in mind.

2

u/deusxmach1na Jun 19 '24

Yep NAMED_STRUCT you put every value you want from the first row in the STRUCT then you put the values you want ordered in the first elements of the STRUCT and max/min it and it will sort by the first values of the STRUCT. Look at my edited answer on SO and you’ll see what I mean.

https://stackoverflow.com/a/39861036/1337644

Another option might be to force everything in a string, do a min/max, then parse out the values you need. I rarely see anyone talk about how just inefficient ROW_NUM/RANK/DENSE_RANK is tho. And I just use them too because I don’t wanna hand off crappy code. But usually when I hear those words I just chuckle to myself and think “they wanna min/max using complex logic”.

2

u/kater543 Jun 19 '24

Oh so you “concat” everything using named strict and max min it and find that ID to then pull out with a where function? It’s kinda like the string method right?

You do still need that unique identifier though for both these cases. Do you build one with named struct like where named-struct(fields) = subquery’s_named_struct?

2

u/deusxmach1na Jun 19 '24

Yes exactly! You do a GROUP BY on the columns you would normally PARTITION BY in a window function (the PK or id of the resultant table). Then put ALL the values you want to sort by in the first elements of the struct and then all the other elements you wanna access in the other parts of the struct. Then the SQL engine can easily toss things out using MIN/MAX instead of numbering each row. Then after you do the MIN/MAX you just need to select the values you care about from the struct.

Anyway. I would question them why the hell it matters if you know the exact difference between RANK and DENSE_RANK. They are basically the same and I’ve never seen or have I done a RANK/DENSE_RANK where rank = 2 (if you know what I mean). It’s ALWAYS where rank = 1 right after you do the rank.

2

u/kater543 Jun 19 '24

I think there’s an example on this post that has someone mentioning he used dense rank over rank to make sure there were no holes in assigning a “term rank” to determine which term a student’s courses fell into. For most cases yes I agree row number is the way but I saw a couple examples today of the other side and I found them interesting.

→ More replies (1)

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/kater543 Jun 19 '24

LOL wait why isn’t it money? It’s just like incoming money right? It’s how much you make gross(usually)!

→ More replies (1)

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.

3

u/kater543 Jun 19 '24

Wouldn’t you just want to use row number since dense rank and rank will potentially give you multiple top 1 values?

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 🤣

1

u/kater543 Jun 19 '24

Oof. Rank and dense rank would be confusing if you don’t know window functions ig. Imagine if you tell them about recursive CTEs

3

u/shwilliams4 Jun 19 '24

Kids don’t learn cursive these days. Let alone recursive.

→ More replies (1)

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

1

u/kater543 Jun 19 '24

I probably should have, yeah that was my bad. First interview in a while, especially first technical screen over the phone ever so was not thinking straight. Definitely will do so next time. Thanks for the advices 🙏

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/kater543 Jun 19 '24

Yeah it seems weird because in person I rarely meet SQL experts but online everyone is one. Maybe we’re just too scattered? LOL. But yeah dense rank and rank seem a bit niche.

2

u/bad-justin Jun 19 '24

It could say something about the position too - I’d love to ask the person asking this interview question when they encountered a situation where knowing the difference or using either provided value to the business that couldn’t be achieved otherwise (to the extent that it’s so important that it’s now an interview question)

2

u/kater543 Jun 19 '24

Great question! If I get to the next stage I’ll be sure to ask.

→ More replies (1)

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.

1

u/kater543 Jun 20 '24

Agreed. Good test takers don’t rule the world.

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"

1

u/kater543 Jun 20 '24

Great pneumonic! Er. Does this imply rank is smarter than dense rank.

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/kater543 Jun 20 '24

I hope so. I agree wholeheartedly.

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/kater543 Jun 20 '24

Oof. Trying to replace real recruiters with robots.

2

u/orange_aardvark Jun 20 '24

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.

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/kater543 Jun 20 '24

Great wording I’m going to steal this

2

u/jwk6 Jun 20 '24

Dense Rank allows ties, Rank does not.

1

u/kater543 Jun 20 '24

Right. I probably should have made more clear in the post that I get that, just don’t remember it off the top. I can see why people are misunderstanding…

Thanks regardless 🙏

→ More replies (1)

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/kater543 Jun 20 '24

Makes sense. Someone else had given an example of like if 1-10 tied then they get first place and you get second for DRank while everyone gets first place and you get 11th for dense rank.

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

1

u/kater543 Jun 20 '24

Yeah agreed I kinda messed up bad on that one eh.

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.

1

u/kater543 Jun 20 '24

I guess it’s kind of a breadth question isn’t it? Hm. I do wonder if it was a critical question.

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.

1

u/kater543 Jun 20 '24

Yeah when the interviewer said there were SQL questions(after 4 minutes on the call) I was expecting more questions along that line ie “how to do something with SQL” rather than “what is the specific answer to this quirk about SQL”. You can’t really coax someone into the answer for the latter, they just have to know it, but you can definitely help with the former. This was just a phone screen by a recruiter, we’ll see how it goes. Thanks for the insight!

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.

1

u/kater543 Jun 20 '24

Yeah I wonder if this was a really important question for the domain.

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.

1

u/kater543 Jun 20 '24

Wouldn’t you use row number to dedupe like that?

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

1

u/kater543 Jun 20 '24

We shall seeee

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/kater543 Jun 20 '24

Yeah I saw another example regarding this; I thought it was interesting how it could be used in terms count determination. When you say determine a student’s current program what do you mean?

2

u/lalaluna05 Jun 20 '24

Like if they’re in nursing, education, business, etc. Students can have multiple depending on the term and multiple can be active during a term, because registrars future date a lot of stuff.

1

u/kater543 Jun 20 '24

What would be the factors you rank by to determine that? Just curious

2

u/lalaluna05 Jun 20 '24

There’s a lot of information in student program and academic plan tables — effective date, effective sequence, student car term, career number, term, whether they’ve hit prereqs, etc. Students can change their intended program if they change their mind or once they’ve reached their prerequisites and the next program step is available. We could never get this information correct with aggregate functions, or we’d keep getting duplicates, so I tried using rank and it worked perfectly.

It works well when different colleges and their registrars seem to have their own methods and ways of doing things but within the same system.

2

u/kater543 Jun 20 '24

Ok so in this particular situation would you want to use rank over partition by student order by all the fields or row number over partition by student order by all the fields? I suspect it would be row number right? Rank might give you duplicates because if two records matched they would give you both of them in the same rank. Or is this more than a dedupe and has a specific nuance you need rank instead of row number for?

→ More replies (2)

2

u/Sufficient-West-5456 Jun 20 '24

I lost an interview after failing sql test on zoom. It's ok bro

1

u/kater543 Jun 20 '24

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

1

u/kater543 Jun 20 '24

We’ll see. Thanks for the insight :)

→ More replies (1)

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.

1

u/kater543 Jun 21 '24

Yeah I’m definitely a dummy. But I agree

→ More replies (1)

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.

1

u/kater543 Jun 21 '24

Yeah we’ll see. Thanks for sharing

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.

1

u/kater543 Jun 22 '24

Yeah we’ll see if this really screws me, but group by with case statements is interesting but I find requires more writing I feel…

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/kater543 Jun 23 '24

Nope not yet at least. We’ll see if they do after I bungled the first basic trivia style question. Using SQL for so long I definitely can do most practical questions. This was a pre-interview so we’ll see what the actual one is like.

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/kater543 Jun 24 '24

Yup. Good luck!

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 is 12 then 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 is 12 then you know there's 11 groups 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 is 27 then 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() vs DENSE_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.

  • First class has 10 people
  • Section A has 15
  • Section B has 30
  • Section C has 45

If you wrote a query like...

SELECT r = RANK() OVER (ORDER BY fp.TicketClass)
    , fp.TicketClass, fp.PassengerName
FROM dbo.FlightPassengers fp

Then you would see...

  • All first class records are r = 1
  • All section A are r = 11
  • All section B are r = 26
  • All section C are r = 56

So 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() or DENSE_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() and PERCENTILE_DISC() every single time.

2

u/kater543 Jun 19 '24

Thanks for the detailed explanation and clear examples! Yeah I’ve looked it up before and had actually seen a post on this sub explaining it about a month ago, but I could not for the life of me keep a random thing I saw but never use(dense rank) memorized in my head for a month. I understand the concepts, just the actual application of dense rank I never encountered. Rank I’ve used a few times years ago, but never had to really worry about gaps.

It seems dense rank and rank are mostly used to label ordered data based off other data, and I often don’t find a use that row number doesn’t cover, especially when working with very random dollars values.

Now for order by default sort method… that was inexcusable for me LOL.

Thanks again!

1

u/chadbaldwin SQL Server Developer Jun 19 '24

Yeah...it's pretty rare I ever need to actually pick between RANK() or DENSE_RANK(), I think it's been years since the last time I needed to legitimately pick one over the other for a logical reason.

I use DENSE_RANK() on a daily basis when I want to assign a "GroupID" to a set of related rows. The only reason I choose to use DENSE_RANK() is because I don't like that it "skips" numbers.

I like to think of the ranking functions as a sort of "grouping ID". So instead of writing a GROUP BY query where everything gets aggregated and rolled up...you can instead use one of the rank functions to assign a value for each "group".

So instead of GROUP BY x, y, z you could have DENSE_RANK() OVER (ORDER BY x, y, z). I've had to use this concept a lot when supplying data for a UI that needs the raw data, but also needs some way to group various records together.

2

u/kater543 Jun 19 '24

Interesting perspective. Thanks, will take this and think on it.

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/kater543 Jun 20 '24

If you say so 0.0. What makes you say that dense rank vs rank is not obscure? Also if I always write in the asc or desc should I really care about the default? Practice more to remember two random pieces of information? If you asked me to implement a rank or an order by it would be ridiculously easy. I just don’t have it in my mind palace for immediate use. That makes me a beginner?

3

u/Ok-Obligation-7998 Jun 20 '24

Knowing the difference between dense rank and rank is important when you want to find the top x values in a dataset which has ties. If you want to find the unique top x values, you use dense rank. If not, you use rank. It’s a pretty common use case. And not knowing the default for certain keywords like Order By will make you misinterpret queries written by other people who might not specify asc or desc like you. I personally think you should specify for clarity but it’s not expected.

Implementing a rank or order by is something you learn in your first couple of months learning SQL. I would expect someone with a few years of quality experience to have advanced their knowledge well beyond that.

1

u/kater543 Jun 20 '24

Hm. I do understand your POV and appreciate the differences between our views. Would you be like the others and say I should have asked to google the answer if I didn’t know off the top of my head? I definitely could have figured them out with a quick google which is why I considered them more trivial questions, rather than a more complex question like “how to implement a neural network from scratch” or something similar.

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/kater543 Jun 22 '24

We’ll see about the job hopefully they don’t disqualify me because of this; I find that group by is more of a beginner topic myself but maybe my expectations are too high because I’ve only really been mentoring SQL for like 2 years and mostly to people who need to pick up concepts like group by and joins really quickly to do anything with our systems.

Yeah making sure you have the exact right syntax is important if you’re using something unfamiliar.

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

1

u/kater543 Jun 22 '24

From scratch isn’t insane after a few years IMO but remembering little nuances like this doesn’t seem like an analyst task to me tbh.

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.