SQLPad Forum question: SQL Question 28

SQL Question 28

I asked a similar question for Question 1 and 2 and the response was that it is an assumption that there is no tie in all of the questions. However, in question 28, we have to find the second largest and return any one. But the solution again uses order by and limit 2. Which I feel will be a wrong answer if there are ties which the question states there can be. Or it is that in interview they aren't interested in ranking and all?
Thanks !

Answers

Comment

In consideration of an interview, it can vary somewhat based on what the interviewer is looking for. Most reasonable places are looking for the primary components of the query, mainly that the logic meets what they've requested, and that you're handling major common issues (ie, nulls, letter case, division, etc) properly. They may ask for additional aspects to your query or changes as this is commonly part of the job.
There are definitely interviewers that are of the type that you better return a perfect query that gives the exact right column names, exactly handle every case, etc. If they're helping you get there (ie, asking something like how would you make sure the column names are correct?), that can be fine. But if they're just expecting perfection with no assistance, typically that's a warning sign of maybe you don't want to work there (or the person interviewing you doesn't actually understand what they're asking - this is common with recruiters / HR level screeners.)

Great insight, thanks Mike.

In relation to this question, I got the same answer but through a different method. I used DENSE_RANK() OVER(ORDER BY COUNT(fa.actor_id) DESC)

Am I correct to think that as long as I can explain my logic in reaching the same solution, that I should be okay? Or will interviewers be concerned about query efficiency? (It doesn't show here, but on sites like Leetcode they measure your query speed)

Edit* I see you can check 'Query Analysis' to see the execution time. In this case, mine took 3.1ms while the official solution took 1.5ms. I also used a sub query instead of CTE, so that may have played a part. But question remains the same - is efficiency in query times an important factor to consider in interviews? Should I spend time to writing better queries even if the end result may be the same?

Andrew, Aug. 21, 2022, 8:44 p.m.

@Andrew - Forgive me for not being able to comment within the chain, but wanted to get you an answer regarding efficiency. I would mainly focus on correctness first, then worry about the performance later. This can be a little tricky as there are many sub-optimal ways to solve a query, but I don't think you need to spend an inordinate amount of time on getting the absolute best performance. If an interviewer asks you how you would improve performance, it helps to have a few ideas up your sleeve of how to do so (and remember that there are often trade-offs in performance.) You could also ask questions of the interviewer in this regard (how often are you running this query? Can you add indexes to assist? Can the data be denormalized in any way?, etc).

Hope this helps -

Mike, Aug. 21, 2022, 10:05 p.m.

Mike (228)

Nov. 12, 2021, 10:38 a.m.

Comment

Can you please paste the answer here

Manav (2)

March 22, 2022, 7:36 a.m.

Comment

I got the answer of TITANIC BOONDOCK being the film with the second-largest cast (1555). Anyone got the same answer?

Haitian (2)

July 3, 2022, 5:33 p.m.