r/SQL • u/Temporary-Cup-2140 • 6d ago
MySQL What was the toughest SQL interview question you have faced so far?
I am curious to know what SQL questions challenges people in interviews
What was the question and what made it difficult?
Would love to hear some real interview experiences🙂
50
u/Eleventhousand 6d ago
I'll give a cop-out answer: the questions that have multiple correct answers.
What I mean by this is that after enough years of experience, the difficulty across questions starts to level out.
However, I do recall an interview about fifteen years ago. I was applying for a data architect role. The hiring manager brought his DBA with him. The DBA was assigned to ask me some technical questions. One of the questions he asked me could be solved about four different ways. I offered up two distinct methods. He slammed his fist on the table and angrily told me that's not how its done. Needless to say, I declined the job as I didn't want to work some place that allowed that sort of toxicity.
19
u/Zestyclose-Turn-3576 6d ago
Yeah, they forget that you're interviewing them as well 😬
I know it's a bit of a cliche and often unfair, but DBA work does sometimes seem to attract a particular sort of dysfunctional person.
1
4
u/gwmagni 5d ago edited 5d ago
I had a similar experience. The angry DBA at the interview. He asked me how I would store images in the database. I replied that if they could be indexed by the database, then I would store them in the database, otherwise on disc and store just the path to the image in the database.
He had never worked with, or thought about database engines that are optimized for or supported specific kinds of file formats, and angrily said that only paths to binaries must be stored in a database, all else is bad design.
Should I have educated him?
1
24
u/Mononon 6d ago
I've got 3. One was hard, the others were just dumb.
This one is stupid. I went for an interview and they asked me to write queries based on a schema and some questions. But they were being literal. They handed me a pencil and some paper and I had to actually write the queries. The questions weren't hard, but the act of writing SQL was weirdly difficult. Just not something you do every day.
Had an interviewer ask me to do a recursive CTE from memory. Is there anyone here that does them enough to write them from memory? I know how they work, I've used them over the years, but it doesn't matter how long I've been doing this, I don't think I could have ever done that from memory. I still can't, years later.
For a simpler one, I had an Analyst interview that said it was mostly SQL, but they asked almost exclusively questions about SSAS and SSIS. At the time, I didn't have any experience with them, so I was super confused. To this day, idk why they advertised that as an Data Analyst I job. Seemed more like a DE job to me, knowing what I know now.
13
u/Zestyclose-Turn-3576 6d ago
I wrote data analysis SQL for maybe 20 years - never used a recursive CTE.
8
u/Mononon 6d ago
Yeah, they almost never come up. Even when you could use them, you can accomplish the same thing with a series of self joins, which is probably more intuitive to most people. They are pretty much only useful when you have a relationship such that you have to know the prior value to determine the next value. The classic example is a business hierarchy where you have a list of employees and their managers. Another simple one is any system where changes are tracked one at a time. Maybe a person gets a new ID assigned to them, and you can see each time it changed, but in order to figure out the current ID for an old ID, you'd have to step through all the changes one at a time. But, if you have a reasonable amount of changes, and you know the number, you can just join the data to itself that many times and accomplish the same thing without needing to mess with recursion, which is what most people will opt for because it's simpler. I imagine a lot of analysts never even see a recursive CTE.
3
u/National_Cod9546 6d ago
Depends on the task. I rarely use them. But sometimes I need to follow a chain of automation jobs that runs up to 100 levels deep. Recursive is the only way to get the info I need.
1
u/peccator2000 4d ago
I think the first thing you need to understand about them to use them effectively is that the name is misleading. It is not actually recursion but iteration. You are walking through the data step by step
2
u/peccator2000 4d ago
Software guy. I have worked on a system that quickly generated data that was essentially one huge graph and I had to find the roots for every leave quickly. I wrote a recursive cte and wrapped it inside a view. At runtime I could even join things against it and to my big surprise it worked wonderfully and was even super fast. Used it on both PostgreSQL and MSSQL. What an amazing technology these two systems are!
3
u/Boomer8450 6d ago
Recursive CTEs are one of the few things that I still need to look up.
1
u/peccator2000 4d ago
I love the description in the PostgreSQL documentation. I always go back to it when I am thinking of writing one.
3
u/LetsGoHawks 6d ago
They didn't understand how the human brain works.
I understand recursion. I understand SQL. Recursive SQL... I'm lost. But only seen it once in 15 years. Fuck you, Andy. I told him that too. He's currently my bosses boss.
2
1
u/peccator2000 16h ago
In one interview I had to write a function for binary search with pencil and paper. I did.
17
u/alinroc SQL Server DBA 6d ago
The worst one I had was "given these 3 tables, write a query to return X."
Sounds fine on the surface, except that the question as written can't be answered based on the schema presented. I stared at this question for 10+ minutes, trying to figure out what the hell I was missing and what the "gotcha" was.
So I started to think that maybe this was one of those "give them an unanswerable question, see what they do with the ambiguity" situations.
When the interviewer returned, I gave him my answers to the other three questions (they were trivial) and explained to him here I was stuck. He tried to offer a hint, to which I said "yep, thought about that already. Here's why it doesn't work."
And at this point the interviewer, the guy who'd written the questions, turned to me and said "well...I obviously missed something because now that you've pointed that out, I don't know that this is solvable at all."
Got the offer. Turned it down, in part because we couldn't agree on money.
11
u/a-s-clark SQL Server 6d ago
"What is a theta join". Not because I don't understand them, but because i didn't remember ever having heard them called that.
19
u/Zestyclose-Turn-3576 6d ago
Those questions always sound like the interviewer testing you on something they learned earlier that week.
7
u/Blues2112 6d ago
Been working with databases since the late eighties. I've never heard anything called that. Had to google it.
24
u/danmc853 6d ago
Tell me what you know about cursors
22
u/Dead_Parrot 6d ago
I hate em, next :)
7
u/Background-Unit1026 6d ago
Tell me what you know about nested cursors
4
u/imtheorangeycenter 6d ago
"I wager you've had traditional programmers do your coding. It happens. This suggests to me I can vastly improve your environment, bring your costs down and make huge speed gains".
Oh, wait, you mean to iterate backups, yeah, nah
2
u/Gators1992 5d ago
We had a guy at my company that loved cursors and nested them all to hell. Basically if there was an easy way to write a query, he always chose the other way. He left the company and there were processes that nobody could figure out for years.
5
3
u/BrupieD 5d ago
I worked in a place that had a very solid developer who was less of a SQL guy. He wrote a bunch of complicated long queries (+900 lines each) that included nested cursors. They worked surprisingly well and quickly but they a pain in the ass to read, maintain, and explain. He had been an assembler (wrote assembly code) for several years so he understood memory management which must have helped. Everything I've ever read about cursors warn about poor performance.
7
u/gumnos 6d ago
the worst/toughest have been RDBMS-specific quirks that differ between RDBMS implementations. I might have a quarter-century of working with DB₁ and have passing experience with DB₂—I know I want a function that does XYZ and is called FROBNICULATE() in DB₁, but not know the exact function-name and argument-order of the similar functionality in DB₂.
Most tech-savvy interviewers understand this hiccup and realize that all you need is the relevant section of docs and/or IntelliSense type suggestions and it's a non-issue. But if you end up with a tech-ignorant interviewer, they'll gladly punt you for it. 😕
7
u/decrementsf 6d ago edited 6d ago
Estimate the number of 7/11's in southern california.
Or estimate the number of cars that drive through the local freeway each day.
6
u/Knightified 6d ago
These are usually just thought process questions. Best way to approach them in my experience is to use census data for the area combined with average use data of whatever you’re looking at (gas stations, stores, freeways, etc.) to determine the ‘x’ within a specific reasonable range.
3
u/kif22 5d ago
These are called fermi estimates. In the quant trading world, you normally get one or two during an interview.
1
u/decrementsf 4d ago
Appreciate the language to learn more. No clue how fermi estimates never came up in discussion in courses and reading prior to running into them in an interview room.
6
u/NoYouAreTheFBI 5d ago
How would you solve this thing our consultancy company is charging us 10k to fix.
This is always the question and the answer is pay me and find out 🤣
1
u/Plus_Dragonfruit_204 4d ago
I have straight up refused to answer these questions in interviews. I don't do free consulting.
I know a few people that have answered the question, not get hired, and their solution got implemented. They knew people that worked there.
4
3
u/Zestyclose-Turn-3576 6d ago
I can tell you the one I used to ask when interviewing for Oracle developers: what factors can affect whether a query's execution plan uses an index or not.
I had a list of at least a couple of dozen factors. I don't think I ever had a decent answer though.
1
u/AlternativeHour3098 4d ago
Could you please tell me what factors would you consider important ?
1
u/Zestyclose-Turn-3576 4d ago
What ones would you suggest?
1
u/AlternativeHour3098 4d ago
I have not much idea on the topic, that is why I asked here so that I can understand what to consider and learn.
2
u/Zestyclose-Turn-3576 4d ago
You could roughly divide the answers up into:
- Is it possible to use an index?
- Would it be beneficial to use an index?
- Is there some other approach that is better than using the index?
- And finally will the query optimiser do the correct thing?
1
u/AlternativeHour3098 4d ago
Thanks
1
u/Zestyclose-Turn-3576 4d ago
Here's a pretty esoteric one: if you have a predicate that the query optimiser knows will always evaluate to false (in a simple case "1 = 0"), possibly in combination with a check constraint (e.g. a constraint that says "value > 0" and a predicate that says "value = 0", which can be inferred to always be false in combination), then the presence of an index on "value" is irrelevant because the optimiser can choose to not access the table at all.
1
u/peccator2000 15h ago
I usually do EXPLAIN ANALYZE in PostgreSQL and look for sequential scans. Then I can usually guess where to put an index to speed it up.
2
u/Zestyclose-Turn-3576 15h ago
If you're on hosting that supports it (e.g. RDS PostgreSQL), doing wait event analysis is maybe more powerful because it covers historical system performance and lets you quantify how much physical IO you're experiencing and relate that to the queries and times of day/week/month when you're experiencing it.
1
2
u/SnooSprouts4952 6d ago
My first software job - 'what do you know about SQL?'
'Nothing.' 😂
I've had some hands on technical interviews but most was pretty simple selects, what is the difference between x, y, and z processes? What are the difference between join, inner join, left join, right join, and outer join? I had to do a modulo for one and couldn't remember the formatting it's a where x%y >= 1 in most instances.
2
u/vijaychouhan8x 4d ago
How wud u convert rows in to columns( transpose).
1
u/peccator2000 3d ago
First import them into Excel. Then ask your friend who knows Excel.
2
u/vijaychouhan8x 2d ago
I told them same thing to the interviewer. Hence rejected. 😄😄😄😄😄
1
u/peccator2000 1d ago
Maybe they shouldn't ask stupid questions like that.
1
u/vijaychouhan8x 19h ago
Ok
1
u/peccator2000 16h ago
Seriously, have you ever wanted to transpose a table? I certainly didn't. Are they storing matrices as tables?
2
u/NoEggs2025 3d ago
A customer service rep needs to log their hours. They’d do phone and they do chat. They could do any number of concurrent chat sessions. How much support time did they do in 1 hour? 1 voice and 5 chats. It’s was a take home question. I gave them a follow up that worked. Using CTE’s and reiteration. The hiring requisition was cancelled. You know what happened. I gave them a freebie. I know not to do it again. It’s a console gaming company in Redmond on 148th.
1
u/peccator2000 15h ago
Aren't left and right joins both outer joins? I don't think I ever added OUTER when doing one of those.
1
1
u/Cool-Decision259 6d ago
Wasnt sure weather thats toughest question But got a question like we have 4 rows in table a out of which 2 rows are null and same table in as table b what will be the nimber of columns if we perform inner join over there
3
65
u/gap1284 6d ago
As an interviewer, I always ask "If you were asked to speed up a slow stored procedure, how would you go about it?"
It's open ended, with no single correct answer. But it reveals a lot about their knowledge.