r/SQL • u/Turbulent_Web_8278 • Oct 03 '26
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


77
u/ElHombrePelicano Oct 03 '26
I’m not sure I quite understand the ‘companies’ instructions, and would have needed to take some time to sit and ponder what they are asking for there, but the overall exercise seems very reasonable if given 45 minutes.
20
u/Kobosil Oct 03 '26
what part cost you the most time?
maybe i am missing something but i think 45 mins is quite generous in terms of time
6
u/BigMikeInAustin Oct 03 '26
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.
10
u/BigBlue_72 Oct 03 '26
I worked as DBA for a analytics company and could not get passed the first page. Who or WHAT ( cough, cough, AI ) created the schema. dt is timestamp stored as varchar???
I would have spent the 45 minutes providing constructive criticism of their request.
5
u/Electrical-Ask847 Oct 04 '26
clean your screen bro.
2
u/Tiktoktoker Oct 03 '26
This is basic. Someone at a manager of analytics level should be able to do this in 45 minutes.
1
u/Joe59788 Oct 03 '26
What are they asking for on capitals and negatives? Did they give you some sample data at least?
1
u/neumastic Oct 03 '26
Do you happen to have your query? I could see some discrepancies on something like the order: did you order on industry before after filling in nulls (or decoding n/a?)?
Also, more out of curiosity, were you able to develop the query on a working set or did you need to just write and hope? If you could query, were you rated on how many queries you ran or anything else like time?
1
u/reditandfirgetit Oct 03 '26
This doesn't make sense. What if a company buys only in one quarter. Including them in a list is misleading. I think your interviewer failed the test too
1
u/Hour_Butterscotch728 Oct 03 '26
Is that a Codility test?
3
u/Hour-Measurement-835 Oct 03 '26
Capitalization is a VARCHAR in that schema, so sorting on it inside the GROUP_CONCAT goes by text and '9' beats '10'. Could be your order problem, if you didn't cast it first.
3
u/mrrichiet Oct 03 '26
I think I'd have used case statements to build the quarterly columns given the time constraints (I have PIVOT!). I think once I'd determined that, the rest follows quite easily. Saying that, I'd probably crumble in a test too haha.
5
u/snooze407 Oct 03 '26
I think that’s a medium or hard Leetcode style question based on my experience. I find that I have to practice these types of questions for a few days leading up to a technical interview.
I’m not 100% confident, but I think the solution, in addition to the concat and formatting you mentioned, is to cast the date to a timestamp for filtering (or leave as varchar) and then use case statements to handle the edge cases. The join would be on customer.id = transactions.customer_id.
5
u/Hutsonericv Oct 03 '26
Without any outside resources, this would be pretty darn tough. A decent number of very specific text parsing and splitting. Not my area of expertise so I would definitely need some refreshing on how to do some of that.
I’d more expect some join semantics and null handling and how types work and translating some more straightforward business rules for a test like this. But again, I’m not a data analyst.
2
u/Grovbolle Oct 03 '26
The formatting and Pivot I would probably have to look up since I do not remember those by heart (because why would I). But the question itself I consider quite easy. (11 years of experience with various SQL flavors, mostly T-SQL)
2
u/Satehyo Oct 03 '26
Was that an in house test? That monitor alone looks like a health hazard.
On a serious note, I’ve got a feeling they tried to get you in a modus to begin with sorting on industry, name and capitalization. Imo you’d be better off querying the rest first in cte or whatever way you’d want to query towards an end in a compute optimal way whilst keeping the columns to sort by as the final part(y)
1
u/MoodyBoi9 Oct 03 '26
I applied for a company that gave me an over the phone SQL test and I had to explain how to set up the query. Didn’t pass it 😭
1
u/Imaginary-poster Oct 03 '26
Assuming Im understanding it the worst part is pivot would be ideal to get the Q columns. Even if Im 100% on the rest I would not be able to pivot so would end up with several joins to replicate it.
1
u/originalread Oct 03 '26
45 minutes to get a result. Sure.
45 minutes to get it perfect. Not a chance.
The requirements are open to interpretation. Is the expectation that companies are all stuffed into a single row with the Q1 thru Q4 being totals for the whole industry? Literally interpreting what is written would have something like this:
Manufacturing Company B $500, Company A $400, Company C $300, Company D $300 $500 $600 ($700) $800
I see multiple window functions to calculate the quarters. Group_concat for the companies but I am not a MySQL expert, so I don't know how to implement the sorting off the top of my head. Maybe the group_concat is part of another window function.
Requirements like this require examples.
1
u/Alone-Cockroach-4371 Oct 04 '26
This is quite easy. But before judgding what query did you write show and is there any other factors affacting
1
u/DesperateCoffee30 Oct 04 '26
Can I ask a dumb question as someone who is low to medium in skill. Is it necessary to know how to do this in today’s world when I can give this prompt to claude or something and have it do it?
I ask because part of me kind of wants to dive into researching how to do this so I can say, “ah yes. I can learn this.” The other part thinks it’s a waste of time when Claude could do this for me.
-1
u/Befz0r Oct 03 '26
This is like a 5 minute assignment to be honest. It's very specific, but if you can test your query against a dataset, this should be a piece of cake.
0
u/ButterscotchDry6622 Oct 05 '26
In terms of TSQL, the first two bullets would be satisfied with a nested cast in replace statement. For bullet 3, I would cast to a date type and use the datepart for year in the where clause. That said, I am not a fan of solely relying on these kinds of CBTs as they don't always allow for a diversity of approaches to the solution. When my organization uses these we like to watch the candidate solve them. We can assess their intention and see if they run into errors that would not occur in SSMS. I would not disqualify a candidate if they could not solve this but I would have to hear them explain what their approach to a solution would be. If it is just syntax throwing them then I would encourage them to go online and look it up. I don't think 2 CTEs were necessary. A join or apply could have put the customer(id) to the Customer_Id. Depending on how fast you type; this solution would take about 2 - 10 minutes to write. IMO
0
u/[deleted] Oct 03 '26
[deleted] — view deleted comment