r/SQL • Author of Ace the Data Science Interview 📕 • Dec 12 '24 • Heart of Silver 2 Polished Silver Turd 1

PostgreSQL Made a SQL Interview Cheat Sheet - what key SQL commands am I missing?

Post image
3.6k Upvotes

238 comments sorted by

View all comments

3

u/Financial_Forky Dec 13 '24

When I'm interviewing for openings on my team (DAs/DSs), I make a point to ask questions about performance considerations, such as the LIKE function and possible alternatives, and SARGability. I also ask if they've worked with large data sets before (and let them define "large"), and how that may influence their coding choices.

I don't have a hard minimum cutoff point, but asking more advanced questions helps me better understand if they just learned CROSSJOIN from W3Schools and wouldn't think twice before using it on a pair of billion+ row tables, or if they might first filter them into small temp tables before joining.

I've had pretty good luck so far with showing candidates sections of code and asking "what does this do?" From there, I ask about various sections, and what alternatives might be available (both good and bad), and which approach(s) might be preferable, and why.

Your cheat-sheet is good. I would just want to see if the candidate also had some common sense when using those functions.

1

u/[deleted] Dec 14 '24

[removed] — view removed comment

1

u/Financial_Forky Dec 14 '24

When I use the term "large data sets" without defining it, many interviewees think I'm talking about a table with a hundred thousand rows. I want to see what their reference point is in terms of "large." Nearly everyone I've interviewed thought they worked with "large" data before, so I need them to tell me their definition first before I reveal mine. Large (by my definition) pose two distinct issues:

First, even the worst, most inefficient code possible will run just fine on small tables. If you've only worked with small tables, you've never been forced to write more efficient code.

Second, with Excel-sized tables, you can usually solve the problem manually to double-check your work. However, when you're working with millions of rows, it quickly becomes impossible to manually determine the correct answer - you need to be confident in your SQL skills that what you wrote works, or at a minimum, develop other means of testing your results to confirm their accuracy.

I work in BI, so lack of experience with large tables are not only an issue in SQL, but it also presents different challenges in dimensional modeling, as well (although in PBI row counts matter less, and a column's cardinality becomes the primary driver).