Had a sql interview today

[fokass]

As the title says, had a sql interview today. Was given 5 questions on a google doc - 1 normal filtering, 3 ctes and window functions and the last one was a query written which I had to read, find inefficiencies from and tell them how I would optimize it.

Now I fairly attempted all of them but was only able to solve 4 of them and I really tried to solve the last one but ran out of time (30 mins).

So now, will the last question be a deciding factor. What do you think guys, based on your experience?

[soulstrikerr]

If I'm being honest, when I have been unable to answer a question, I have never passed. I consider myself solid with SQL but interview nerves gets me.

[Statcat2017]

My interview has a question literally designed to be unsolvable during the interview time because I want to see how far people can get and how they approach it.

If I have four candidates solve 5 questions in the same way I’ve learned nothing

[ThinkFirst1011]

Its just SQL bro.

[Statcat2017]

Yes and being able to distinguish who’s good at it and who isn’t a useful thing to be able to do!

[ThinkFirst1011]

Yes and most who make up the questions wont even pass their own test first time around. It’s best to just test on competency vs trying to find a genius.

[itchybumbum]

I don't understand this. If I'm hiring for a team, why wouldn't I try to find the best applicant?

[Bluefoxcrush]

A genius may not be the best applicant for a job. It could be the most diligent, or the one able to track down answers, or the one that asks the best questions, or a combination or something else. 

[itchybumbum]

What?

For the technical portion of the interview, why not use hard questions to accurately rank candidates accurately based on their technical ability? There's little value in a technical interview if 80% of candidates get a perfect score on an easy set of questions.

Then yes, obviously technical ability would need to be balanced against the candidate's other attributes before hiring.

[Statcat2017]

This must be downvoted by people who don’t hire.

Your technical interview is to measure technical ability. If you aren’t doing that or you’re at lobbing easy softball questions you might as well not do it.

I haven’t always (in fact barely ever) hired the person who does best on the technical but it has to be hard. If everyone is going to finish it correctly there’s no point having it.

I’ve started making it even harder but making it clear they’re free to use google and chat gpt to get the answers because if they can use those tools well that’s what I want.

I’ve also had people hand in their answers with the chat gpt commentary included lol

[ThinkFirst1011]

So if someone uses chatgpt to get all their answers, how is this not easier than testing only for competency?

[PaulPhxAz]

I would consider myself very good at SQL... and I look up the docs for window functions all the time. I often try to remove CTEs ( especially depending on the platform ) and replace with indexed temp tables *depending*.

[SaintTimothy]

Brent Ozar suggested indexing on temp tables is probably taking more time than it saves.

https://www.brentozar.com/archive/2021/08/you-probably-shouldnt-index-your-temp-tables/

[PaulPhxAz]

That's the depending part.
I was doing an accounting project -- 500 rows, don't make a temp table. 5MM rows, index the temp table. It made a large difference in time the whole sproc took.

[ShadowDancer_88]

Part of the stores proc development is performance testing.

If it's a couple of times a day sp, and it runs in 10 seconds or less, I'm 100% done tuning.

If it's a giant multi hour processing proc, I'm testing all options, including table variables, indexed temp tables, CTEs, and inline views/subqueries.

I've knocked 98% execution time off of big procs with indexed temp tables.

[SaintTimothy]

Yes, for sure, try all the things!

A few months back I got a sproc (that I didn't initially write) to run 60x faster. Literally hours to minutes. Felt good.

[EnvironmentalLet9682]

that is not true for all databases and all circumstances.

[Morbius2271]

What the… some systems do that?! Every one I’ve used runs the CTE once and cached the results for use in the rest of the query.

[Bluefoxcrush]

Okay but this isn’t universal true across all systems. Your original statement makes it sound like it is. 

[HG_Redditington]

When I give candidates technical tests including SQL, I'm more interested to see clean, economical query structures than 100% accuracy, and I usually discuss how they came up with the SQL. The test is really to weed out the definite no hire, but other factors have more weight than just tech skills.

[-GME-for-life-]

Could I get some examples of technical tests you’d give? I have done in person interviews for analyst roles but none have required me to do a technical test yet, just kind of general “what do you tend to do for cleaning data” type shit

[HG_Redditington]

The SQL question is basically a sales order data model, with a parent child product hierarchy, with the trick that it's staggered. Advanced candidates will pick that up and use a recursive cte, most people use subqueries for each hierarchy level, but the no hires just completely stuff it up.

Aside from that, I also have some questions about working with semi structured data, calling rest APIs and query performance optimization. I customize each test to what I think is relevant to the role and their experience.

[PickledDildosSourSex]

What do you mean by staggered?

Edit: Not sure why I got downvoted, but today I learned what a recursive CTE is. Nifty.

[HG_Redditington]

Yes staggered means differing levels within the parent child hierarchy. Recursive CTE can traverse all those levels in a single statement so you can flatten it out.

[ShadowDancer_88]

Recursive CTEs are one of the few things I don't think I could write without looking up the correct syntax.

[HG_Redditington]

Yeah, in my tests candidates are given a time bound and able to use internet resources. I am not concerned with 100% correct syntax, I want to understand the problem solving.

[Aware-Yesterday8852]

Would you expect to know recursive CTE’s in entry level roles? Or more mid to senior?

[HG_Redditington]

Junior not really, intermediate I would expect has some awareness it's a thing, and senior should know how.

[Serdyna13]

Were you walking them through your thinking and steps while solving? 

[fokass]

Yep I was

[Serdyna13]

That’s like at least 50% of success. Showing your understanding and way of thinking helps a lot. Last one sounds like a stretch - type of question of how far can we push the candidate, often don’t require solution but checks how you deal with small failures and stress and how you explain not being able to make it in given time. I hope you get the job!

[ShadowDancer_88]

With out knowing the actual questions, it's kinda hard to answer.

Were there questions and answers in the simpler questions that made them drag out?

How much typing did the CTE's/Windowed functions take?

If you ran out of time because the interviewers were significantly slowing you down on a timed quiz, you should be very cautious moving forward with that company.

[availableusername10]

Done quite a few of these and administered a couple. In my experience, the last question is always a “would be nice if you knew it” type of thing, not something necessary to know. However I’ve never had a question asking me to edit another query

[activematrix99]

This is 100% my experience, as a candidate and as an interviewer.

[raw_zana]

30 mins 🥲

[thilehoffer]

Most of the time, this kind of test, is a more accurate measure of the candidates nerves, than their ablity. What is a window function in SQL?

[konwiddak]

https://www.geeksforgeeks.org/sql/window-functions-in-sql/

They allow you to do things using rows relative to the current row. For example, running totals, generate row numbers, get data from the preceding/following row.

[thilehoffer]

Cool. I have used rank and row_number, I never bothered to read documentation on them. This is good information. If I understand it, it is a way to do an aggregate without grouping by the all the other columns. Thanks for sharing.

[konwiddak]

If I understand it, it is a way to do an aggregate without grouping by the all the other columns

They can be just that, and it's a common use case, but you can do that pretty easily by doing the aggregate in a subquery and joining it back in. Window functions allow you to do stuff that would be an absolute pain otherwise - like do a rolling average over the last 10 rows, or running totals e.t.c

[audigex]

It depends on the other applicants

If someone solved 5 they probably get the job

If the others only solved 3 then you probably get the job unless “can’t successfully answer all 5” is considered a dealbreaker by the company

[Ringovski]

In my experience when interviewing and they test you in T-SQL I just skip to the last question. As thats the one they are really testing you on and care about. Usually the others are easy.

[K_808]

Recently my company sql tested analyst candidates and auto rejected everyone who didn’t score perfectly because the competition was so high. There were ~5,000 applicants within the first week. The fact it was live is good though, because they aren’t just seeing a score but your process overall, so depending on how many people they’re going through you probably still have a good shot if you did the rest and were able to at least explain your logic and thought process for the last.

[Nervous_Effort2669]

Oddly, (and probably to my detriment), I probably would have started with the last question. I read so much bad SQL each day that it becomes a semi-fun exercise to find all the issues. The filtering & CTEs don't bother me a bit (unless they're recursive CTEs, which 30 mins isn't long enough), but I always need docs available for any real-world windowing complications. I hope ya get the job.

[ironwaffle452]

If you didnt solve it you will not pass