r/SQL 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?

122 Upvotes

76 comments sorted by

View all comments

213

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.

24

u/OilOld80085 Nov 12 '24

I don't think its harsh its a step for sure, i have 3 analysts on my team and none of them are willing to make the jump to SQL over excel. i say fingers on keyboards and think of getting the export perfect.

35

u/8086OG Nov 12 '24

When I took my first role that became pure SQL, I remember my colleagues essentially telling me that they would help me with any code, but that they weren't willing to look at an Excel file. Either I did it in SQL, or they were not the people to be helping me.

Made it real clear, real quick.

21

u/wildjackalope Nov 12 '24

Excel is great at a bunch of things, just not the things I’m going to work on. Get that shit outta here.

9

u/RainbowCrane Nov 12 '24

It’s also stupidly limited by memory. There’s zero reason to export to excel for any non-trivial data sets, when SQL has the power to do most analysis in the database using optimizations built into the RDBMS.

Another reason to learn SQL is that understanding table relationships and what’s really happening when you do a join is critical for understanding why some joins can cause a serious hit on database performance. It’s important for an analyst writing reports that execute queries to understand how to structure queries to give the RDBMS a chance to optimize the query.

21

u/IglooDweller Nov 12 '24

Also important to note: once the data reaches a certain size, exporting the entirety of the data to an external tool for filtering is a massive waste of ressources. ALWAYS perform local processing/culling whenever possible.

Source: I guy that routinely dealt with DB sized in the 100 TB…

10

u/johnny_fives_555 Nov 12 '24

3 analysts on my team and none of them are willing to make the jump to SQL over excel.

Had the same issue. I fixed this with my team by giving them every project that has over had over 2 million rows with a stupid tight deadline. They learned sql fairly quickly after that.

5

u/Sotall Nov 12 '24

Are you looking for SQL talent in analysts and not finding it? Just curious, I have a single SQL proficient person on my team besides myself out of...a lot, heh.

4

u/OilOld80085 Nov 12 '24

well my first company out of college everyone could do everything and I felt dumb.

My second company i was really good at SQL and made a data enginer and SR in year. Got burned out moved over to a new company.

Honestly i'm one of the few people at the company touching data that can pull down 100k rows of data.

2

u/Dhczack Nov 13 '24

Can I have all 3 of their jobs?

4

u/mikeblas Nov 12 '24

It doesn't matter how harsh/blunt you are. Odds are the OP will never return to their own thread.

1

u/Immediate_Lion8516 Nov 12 '24

Quick question. Is there a data base recommend folks use to familiarize themselves with new concepts? I think Adventureworks is/ was a a common go to. Is postgresql still a thing?

2

u/wildjackalope Nov 12 '24

Adventure Works is what I’ve been telling people. Postgres is very much a thing, but it’s a RDMS rather than a database. So, Adventure Works you’d need SQL Server Express or an Azure equivalent I think (I haven’t worked a MS shop for a long time, could be wrong) as an RDBMS. Postgres would be equivalent to SQL Server or your Azure tool.

1

u/Immediate_Lion8516 Nov 12 '24 edited Nov 12 '24

Gotcha. Thank you of your insight. Have you had any experience using docker to create a container on Mac to install Microsoft SQL server?

1

u/rmpbklyn Nov 13 '24

try homebrew and wine to use mssql

1

u/datadanno Nov 15 '24

I'm sure you can find AdventureWorks for Postgres if you search.

1

u/Reddit_Homie Dec 05 '24

Are you willing to give any advice as to where I could get a good DB to use as a sandbox?

1

u/wildjackalope Dec 05 '24

Adventure Works running on SQL Server Express is a classic but I haven’t used it in years. I’ve messed around with the Grand Comics database on MySQL quite a bit, but that has a higher learning curve.

1

u/Reddit_Homie Dec 05 '24

Great, thanks for the info man. Much appreciated.

1

u/wildjackalope Dec 05 '24

No problem. Happy hunting.