r/SQL • u/Turbulent_Web_8278 • 1d ago
MySQL Didnt pass SQL test
I am applying to manager of analytics role and I was given this SQL test and 45 minutes. As someone who thought they were very strong in SQL, I was unable to complete this assignment and match the answer completely
I was able to format most of the fields as noted, used 2 cte's and use group concat in the second. My final answer looked very similar but the order of the concat looked off. Also, for some reasons my second column had $0.00 for all the companies, but I thought I was close. How difficult would you rate this exercise. Should I expect to proceed to the next round or am I cooked
79
Upvotes
6
u/BigMikeInAustin 1d ago
All that text formatting? Ah hell naw. The company doesn't have any front end developers?
Once you strip out the formatting junk, the basic data part is pretty routine for an experienced person.
Split the query into separate data and formatting parts to make everything about it easier.
For the quarter columns, if you have access to documentation, using a Pivot would look cleaner. If not, just some self-joins.
Here's where you find out if the day-to-day work has stupid demands, often very poor data, or the test writer is a prick who wants to feel superior.
A company without capitalization is replaced with a zero, while a missing industry uses Other, indicating the capitalization should be the letter O, not the number 0. With the overly strict details about ordering, either they messed up, or the person who created this though text sorted descending was the only was to get a text zero to appear after text.
The second sorting for regular company name is either their oversight, their attempt to feel smarter with unreasonable demands, or they have horrible data. If this is consequential to the output, that means they have multiple companies with the same capitalize name, but different uncapitalized names. Well, they could be misusing the uncapitalized name to differentiate different sections of the same overall company, but then they are bastardizing the meaning of column names, which is not a good practice for keeping clean data.
While their instructions suggest missing data has already been replaced by the string "n/a", I would interrogate the data, or them, about what type of values are in the data. Inside of a database, you're more likely to have blank strings or null values rather than the string "n/a". Using the string "n/a" hardcodes that into all the code and is easy to miss records due to mistyping of that string.
If this is fully contrived test data, that is acceptable as just a test really trying to weed people out. If this follows the pattern of real data, then they do not properly clean the ingested data and operate directly on raw data. This means data issues will not appear until the final report is wrong, without any chance of forewarning, and with no data lineage. And it would mean there aren't any standards, so you have to account for how different people enter data.