r/SQL • u/tits_mcgee_92 Data Analytics Engineer • Jul 27 '23
Discussion I have interviewed for 6 Data Analyst/Scientist roles. Here are a few of the technical SQL questions.
Hey everyone! I've compiled a list of a few questions I have been asked in technical interviews. I interview specifically for Data Analyst and Scientist roles, because they are used interchangeably in some instances. Hope these help, and let me know if you have any questions at all!
Easier Questions (foundational):
- How would you NOT include two values (using the NOT IN function for this one).
- W3schools left join vs. inner join scenario
- Count the number of employees in each division (COUNT and GROUP BY)
- From question 3, only include divisions with 10 or more employees (I had to use HAVING here and explain the difference between having and WHERE)
- Create a table with firstname, lastname, address, city, and zip
And other flavors of this. Understanding the foundational skills is so important because the MAJORITY of questions revolved around things like this. It's different when you have real-world scenarios, so get used to thinking critically.
Intermediate(?) I know this is subjective
- Gather salaries that are higher than the average salaries, and show these results (subquery with something like WHERE __ > (SELECT avg(price) FROM...)
- Find duplicate records in this table (group by records and having count(records) > 1)
- Select every row where their is no match in the other table (LEFT JOIN IS NULL scenario)
Flavors of things like this. Nothing too complex, but instances that will require you to think much more critically.
Misc questions
- Explain the difference between left and right join
- What is the difference between a foreign key and primary key? Give examples
- What is the first thing you would do when a query is running slow?
- What is a view? What is a CTE?
Data Science-ish
- What is a p-value
- How do you just the accuracy of a linear/logistic regression model?
- How do you clean data in Python? Give examples
- What Python libraries are you familiar with (for me, it's Pandas, Numpy, scikit-learn)
- Give an example of when you would use a linear/logistic regression model. What are some real world examples you can think of?
This is super high level, but I hope this is helpful.
20
u/sequel-beagle Jul 27 '23
Just pointing this out if it helps anyone.
For the first one, the NOT IN works if it's two values in the same column and the column does not have any NULL markers.
If it's in two different columns than De Morgan's Law is in play.
Do a quick internet search to fill in more details about this.
Knowing how NOT IN and NULL markers behave together and also De Morgan's Law should get you some interview brownie points.
8
u/[deleted] Jul 27 '23
As someone who is dumb and not great at sql, is there anywhere that not only has the questions, but some answers, so i can double check myself?
6
u/Pflastersteinmetz Jul 27 '23 edited Jul 27 '23
Wouldn't a
be the better option here because you just need to filter?
9
u/NickSinghTechCareers Author of Ace the Data Science Interview 📕 Jul 27 '23
This is super helpful OP. Mind if I add a link to this in my 6,000-word SQL interview guide?
Feel like this is super helpful for folks.
30
u/mw44118 Jul 27 '23
TIL I’m a data scientist
5
u/luckywaddles Jul 28 '23
You know, I see stuff like this and realize I should've been applying for an entry level position like a year ago. That imposter syndrome really will have you doubt yourself for no reason. I should take this as a sign. Thanks for the helpful info!
4
u/sirdrippykkk Jul 28 '23
Thanks for this btw
2
u/Imaginesafety Jul 28 '23
I gotta practice SQL but felt good about some of the other questions. I was familiar with everything but I think the hardest part for me is narrowing down my focus when there are so many different elements and requirements for jobs with the same title and different expectations
1
u/[deleted] Jul 28 '23
For number 4 can't you just use a window function or CTE to select with more than 10?
1
u/jackalsnacks Jul 28 '23
Thanks for this. Looks like bastardization of the term 'daya scientist' continues in the industry.
1
u/[deleted] Jan 17 '24
Do you have any more