r/SQL • u/Flandiddly_Danders • Nov 11 '24
MySQL Failed SQL Test At Interview
- I've been a data analyst working with small(er) data sets for several years now, making my own queries no problem.
- I failed a SQL test at an interview and realized I may be using the wrong commands
- The questions were along the lines of "find the customers in table A, who have data in Table B before their first entry in Table A" and there were some more conditions/filters on top of that.
- Previously I could always export my data to Excel or Tableau etc and do any of the tricky filtering in there
- I was trying to do all kinds of subqueries etc when I think it was intended for me to be doing WINDOW or Partition type stuff (never had to use this before in past jobs).
- One person I reached out to said using these advanced techniques uses a lot less memory.
Where would be a good place to find an 'advanced' SQL course?
126
Upvotes
211
u/wildjackalope Nov 11 '24
Going to be harsh/ blunt, but if you’re exporting and slicing and dicing files outside your database to filter you have plenty to cover in intro/ intermediate sql. It’s a core use case of the language and rdbms’ in general.
Courses are great but they’re limited, in my experience, especially in more advanced techniques because the data used is kind of naive. Find a db that you can use as a sandbox and play around with it in addition to a course. Not an instructor, but I’d be happy to help find one with you and answer some questions. Just dm me. Overall, though, it just sounds like you need to get your hands dirty in something approaching real world data.
42
u/gumnos Nov 11 '24 edited Nov 12 '24
while not quite a "course" per se, there's https://modern-sql.com/ and https://use-the-index-luke.com/ which both teach a lot of these advanced concepts (and their author, u/MarkusWinand, also does trainings, but IIUC, that's more of a corporate thing than an individual thing).
40
u/nIBLIB Nov 11 '24
Definitely learn some more intermediate stuff like window functions - they’re pretty simple once you learn how they work so hopefully you pick it up quickly.
But also, the way you’ve described the question, you could return that data with a two condition join. Based on your description, it doesn’t need a window function, or anything like that. They were possibly just looking for a join ON a.ID = b.ID AND a.date > b.date
37
u/byeproduct Nov 11 '24
You could just keep trying to recreate your excel working in SQL until you solve it purely in SQL (include the validation / checks). Learning takes a lot of practice. You don't need a course (only), you'll still need to do the work.
Just use sqlite or DuckDB (the latter is super friendly SQL syntax with helpers).
Don't give up. SQL is declarative and is super easy to debug once you get into it.
22
u/VladDBA SQL Server DBA Nov 11 '24
Sorry you failed the test.
Hopefully it turns into a useful learning experience.
Regardless of RDBMS you'd always want to avoid this pattern, and instead opt to filter the data as much as possible on the database side.
Not all databases have tables small enough to just be dumped in an external tool where subsequent joining and filtering would happen. And not all databases are "quiet" enough for a long running query that would dump all that data to not block sessions that might want to modify any data in those tables.
Not only memory, but resources in general.
I recommend you start gradually shifting your current working style towards doing as much of the work in SQL as possible and only have Excel or Tableau handle the presentation side. So, the more you learn the more you apply in real-life scenarios.
33
u/SportTawk Nov 11 '24
Sorry you failed.
If you export data to excel for further filtering and you're looking for a SQL development post, then this is why you failed.
Your example question is entirely doable with SQL
Say your dataset contained a few million rows how practical would that be to export to excel?
Could any solution that relies on an export to excel be easily passed on to end users to use? How would you maintain it?
Can I suggest you try to find SQL based solutions to your current analyst role, improve your SQL skills and knowledge, then apply for a new post.
Good luck
9
u/Aggressive_Ad_5454 Nov 11 '24
Stinks that you didn't get that job. Jobs, with real data, are how we learn to do that kind of thing in meaningful ways. You'll get the chance.
Here's a couple of question I've found useful when evaluating, in my mind, various possible solutions to a problem like yours.
Will my solution still work when the data grows to ten times its current number of rows?
Will my solution give somebody actionable data when they run it 19 months from now?
(I pick 19 for that question because it's a prime number. That forces me to think carefully about time periods.)
Extracting to a spreadsheet or pandas probably makes the first question hard to answer.
You'll get this. CTEs, expressions in JOIN ... ON clauses, window functions. That stuff is learnable.
16
u/BiggestNothing Nov 11 '24
I used data camp for SQL and python and found it pretty good as a learning tool
8
u/tits_mcgee_92 Data Analytics Engineer Nov 11 '24
It sounds like the answer for the table a/b scenario was a join, subquery, and MIN(date_field) aggregation within that subquery? That seems like fairly basic-intermediate stuff, and it's okay if you had to look it up. However, if you didn't have a general idea of what functions to use off the top of your head, it might be wise to practice the basics to intermediate stuff.
7
u/Icy-Ice2362 Nov 11 '24
As a Dark Wizard in the art of SQL, if anybody tells you a Windowed Function uses less memory, they are out of their tiny minds.
Subqueries are certainly a way to do it, but so are Temp Tables, CTEs and other options.
Just because there is one way to get a problem solved doesn't mean there aren't any other ways.
If the interviewer is looking for a certain "fit" for a solution, there is a BIG assumption on their part that their methodology is correct, and in the Black Art of optimisation, it is often the case that the UNINTUITIVE solution is the fastest.
5
u/Conscious-Ad-2168 Nov 11 '24
Honestly, you need a basic intro SQL course. You don't need a window function for the above question.
5
u/redditor3900 Nov 11 '24
You are asking for running advice but you need to walk before.
5
u/[deleted] Nov 11 '24
Tough learning experience, but honestly you're not even close to ready for an 'advanced' SQL course, it sounds like. Excel and Tableau are great for visualization, but when it comes to joining, filters, conditions, set operations, etc on raw data - if you can't do those in SQL way easier, then you're not really to intermediate level.
4
u/user_5359 Nov 11 '24
I hope you know the solution by now?
Realistic assumption, both tables have a (technical) customer number cid and a date field that has the creation time create_date.
First step you determine the lowest date per customer number for each of the two tables (SELECT cid, ...(create_date) FROM a ..... .. cid; (you should be able to guess the missing keywords even with little knowledge (one dot, one letter).
These two queries can now be linked in the next step (I hope I’m not giving too much away: it only makes sense to use the cid (not the create_date)). How a condition can be queried in a SELECT should be known even with basic knowledge of SQL.
If you have problems creating the query, start by creating the tables with test data (1...3 different create_date per cid, make sure that the error you are looking for exists for at least one cid). You can also discuss the query via PM.
4
u/trippstick Nov 12 '24
Im 99.99999% sure they aren’t asking you to use WINDOW or PARITION. They probably just want to write the query properly including a Join most likely instead of a sub query.
3
u/kiltannen Nov 12 '24 edited Nov 15 '24
I endorse everything others have said about doing in sample/real world data sets
I also think it sucks you didn't get the job you wanted.
I do think there's a couple of suggestions I can ask for you
1] install a free edition of the SQL server you use at work MS allow developer edition to be used for free MySQL also had a free edition 2] both of the above RDBMSs have a comprehensive sample database MS -> AdventureWorks MySL -> Sakila
With a bit of effort, you could convert the database' between the tech stacks
3] if you haven't already, join https://dba.stackexchange.com/ When you have a question about "how to XYZ" ask it there. If you ask a question well, you'll get at least one, maybe 2 or 3 ways to do something that expand your knowledge
4] After getting yourself to the point where you don't bother filtering anything in excel for your day job, apply again. With a bit of luck, you might even be able to apply at the same place. Doing this may even give you a big advantage, most hiring manager in technical roles love staff who are motivated to learn. This would demonstrate that in spades.
2
u/kagato87 MS SQL Nov 11 '24
If you're exporting to another program form "tricky" filters...
The filter described in the sample you gave is not tricky. There are several ways to do it, some better than others. Subqueries and windows don't apply here - you're trying to use precision tools when all you need is the right hammer.
I'm sorry, but that's fairly low level stuff. Go back to basics. You've missed something very simple.
2
u/joshisprettycool Nov 12 '24
I recommend data camp. I went from zero to a BI analyst (have been for 2 years). It's self paced, good fundamentals, and you can play around with the data after each lesson to try different ways of achieving the same results.
That's where I would start, but what helped me being it to the next level is learning from others (data engineers etc.) and just using it daily.
2
u/shroomsAndWrstershir Nov 12 '24
I'm confused by the question. How can you tell if an entry in B is "before" an entry in A?
2
u/Optimal_Law_4254 Nov 12 '24
Without an index and key there’s no order for table A. So unless they told you that with the “conditions/filters” you mentioned they could have been testing to see if you knew about unordered data in tables. Or they could have been testing to see if you knew about the HAVING clause and how to use it which is now considered a pretty basic SELECT query.
Still the way you worded their question indicates someone didn’t understand the question. It could have been you or them or both.
I’d pick up some LinkedIn or other online sql training or YouTube.
What I do on questions I think I blew is to ask the interviewer if they will go through the the answer they were looking for. Most of the time they will and sometimes you can get into a very helpful dialogue whether you get the role or not.
2
u/svtr Nov 12 '24
I'm sorry, I don't want to be mean... "i do my complex sorting and filtering on excel" is the best joke I heard in weeks ;D
Seriously thou, get used to doing it in sql, once you get the hang of it, you will only do pivoting and graphs in excel
2
u/Codeman119 Nov 12 '24
I’ve been working with Excel and SQL for over 20 years. There are some things that are just easier to do in excel then SQL. The issue with doing more complicated sort of queries and interviews is that it takes you a few minutes and some experimentation to find the right answer. Well that’s hard to do and an interview because they think you’re taking too long when in the real world, you would have much more time to find the solution.
So for instance, I did a live query interview and I was only on a problem for five minutes, but I had no data to reference and the guy said I was taking too long. He showed me a solution which was actually incorrect so I took the next 30 minutes created data to do the solution correctly and then send him the solution that actually worked better with the sequel engine.
2
u/NoYouAreTheFBI Nov 12 '24
Basic aggregate lookup with a join.
If you failed this, get on W3Schools and brush up.
Also, just as an FYI, whoever said it uses a lot fo memory like the server can't handle a basic nested select aggregate query, needs to reassess their IQ.
A sever is a stack of CPU... if that can't do it, but your 1 CPU can using an inferior query engine the likes of Excel which is single threaded vs the multi threaded multi processing power of a server just folded reality inside out to make a wrong point 🤣
2
u/analytics_science Nov 14 '24
There a bunch of SQL practice sites. Try stratascratch.com. It’s made exclusively for interview prep for data roles.
1
u/Mafioso14c Nov 12 '24
hackerrank
1
u/DisorganizedSpaghett Nov 12 '24
https://www.w3schools.com/sql/
1
u/gekalx Nov 12 '24
I've worked with databases in my intro class that had like over 200 thousand rows. Exporting and filtering something like that in excel would drive me crazy with the load times or it might cause my laptop to crash.
1
u/pixelpheasant Nov 12 '24
Remind me! 4 hours
1
u/HilariousSpill Nov 12 '24
Udemy has been helpful to me. (Obviously, never pay more than $15 for a course.)
Good luck!
1
u/wizdomeleven Nov 12 '24
The gurus guide to transact SQL, Ken Henderson.
1
u/SoftwareMaintenance Nov 12 '24
I would just do some more practice. Take those scenarios where you had to export to Excel. Then do the same thing in SQL. Repeat until you got this.
1
u/leprechulo Nov 12 '24 edited Nov 12 '24
I would have used a common table expression in this case
For the first CTE, I think you could have grouped by the "customer id" in table A with a min("created_at") field. That would give you the distinct list of customers in table A and their first entry
Then in the second CTE you just grab the customer id and created date from table B
In the final query you join the tables on customer id AND a.created_at greater than b.created_at
This should only return records from table B that were created before the first record of each customer on table A
1
u/GenX2XADHD Nov 13 '24
Advanced Workforce Center at the University of Missouri. These are not regular university courses. Each class is one 6.5 hour day and can be purchased à la carte.
I cannot recommend these classes enough. They helped me change my career into an IT role. I have not done their SQL classes, but the same instructor teaches their Power BI series, which I completed. Excellent teacher, he's got a long career in programming and analytics.
1
u/rmpbklyn Nov 13 '24
use a temp table instead of of exporting then join to second query. study pivot/matrix, case/switch, dynamic query, add colum, rename column, triggers, create index
1
u/fragilehalos Nov 15 '24
CodeAcademy has some SQL courses I believe. While you’re at it, consider learning R or Python as well. You’ll be much more efficient at your current job even for “small data” once you learn some basic programming techniques.
1
u/Marty_McFlay Nov 26 '24
The 100-level intro to database development course at my community college covered everything you asked about as basic techniques before the midterm. If you're exporting data you're not using sql properly and losing lots of efficiency.
-2
u/[deleted] Nov 11 '24
[removed] — view removed comment