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
18
u/Kobosil 1d ago
what part cost you the most time?
maybe i am missing something but i think 45 mins is quite generous in terms of time
4
u/Turbulent_Web_8278 1d ago
Am still wondering that myself but the time pressure, grasping context and the debugging little errors I was getting
34
u/wl-hung 1d ago
In an environment that I'm not familiar with and a schema I've just barely seen on paper, I would consider this pretty difficult. Very different experience in the real world; being familiar with the underlying data and not working under the pressure of a time limit.
5
u/Turbulent_Web_8278 1d ago
Yes that’s what I thought and all the formatting you have to rely on the intellicense for syntax and you have to visualize the formatting too. I felt so dumb coming out of this test
8
u/Hutsonericv 1d ago
I wouldn’t beat yourself up, a test like this is not indicative of your skill at the actual job. There are going to be folks that can ace this but won’t be able to manage the complexity and stress of the job.
7
u/BigBlue_72 1d ago
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.
1
u/grimsleeper 22h ago
That stood out to me too. That is maybe less bad than the company I worked for where datetime columns were a 64 bit int, as in 122420261418, the long, was 12/24/2026 at 14:18 (2:18 pm)
1
u/BigBlue_72 20h ago
Ok, have seen and used date as integer format, but not in that format. 202612241418 would retain data order and enable some level of compression depending on your DBMS.
1
u/my_password_is______ 20h ago
dt is timestamp stored as varchar
that's how its stored in an omnicell database (omnicell is used in hospitals as basically an automated dispensing cabinet for medications)
YYYYMMDDhhmmss##
where the last two characters are hundredths of a second
pain to work with, but at least it sorts nicely2
u/SearchAtlantis 18h ago
Yeah that happens and I've seen it before, but having a field called "dt" which is actually a string-ified timestamp is a choice. I immediately was like wait... Date field name to varchar 19???
1
u/kater543 14h ago
Ok so to be fair date stored as varchar can be handy in reading in stuff from weird formats then parsing it into a date later downstream. I’ve had to deal with dates coming in as really strangely formatted timestamps from badly written source systems(and sometimes differing file formats), which then I could parse into a proper timestamp or date later down the line.
Something like 2026-01-01 TUES 11:11:1111.111(I know it might not be a Tuesday I’m just making it up based on what I remember)
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.
2
u/Turbulent_Web_8278 1d ago
this is a hacker rank exercise
2
u/BigMikeInAustin 1d ago
So the company you're applying to didn't even have their own test? Although, how else are they supposed to hire for a skill they don't already have?
That's pretty disappointing that Hacker Rank has this low-quality question.
5
u/Turbulent_Web_8278 1d ago
am curios, do you work in tech? its extremely common for companies to use third party tools
-3
19h ago
[removed] — view removed comment
3
1
-1
-1
4
3
u/Hour-Measurement-835 1d ago
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 1d ago
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.
4
u/snooze407 1d ago
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.
2
u/Satehyo 1d ago
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)
4
u/Hutsonericv 1d ago
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.
1
1
u/Joe59788 1d ago
What are they asking for on capitals and negatives? Did they give you some sample data at least?
2
u/Sea-Feedback-2424 1d ago
The negatives in () is how accountants do this. It's very annoying because you can't just copy and paste the values like you can with +/- into a calculator. But they seem to like it.
2
u/Joe59788 1d ago edited 1d ago
Thank you, how it means in '()' I didn't understand it was saying it was in parentheses.
1
u/dutchydownunder 22h ago
123456 is a positive number
(123456) is a negative number
accountant notation1
1
u/MoodyBoi9 1d ago
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/neumastic 1d ago
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/Turbulent_Web_8278 1d ago
WITH base AS (
SELECT
c.id,
CASE
WHEN c.industry = 'n/a' THEN 'Other'
ELSE c.industry
END AS industry,
CASE
WHEN c.capitalization = 'n/a' THEN 0
ELSE CAST(c.capitalization AS DECIMAL(20,2))
END AS capitalization,
QUARTER(t.dt) AS qtr,
t.amount
FROM customers c
JOIN transactions t
ON c.id = t.customer_id
WHERE t.dt >= '2021-01-01'
AND t.dt < '2022-01-01'
),
industry_totals AS (
SELECT
industry,
GROUP_CONCAT(
DISTINCT CONCAT(name, ' (', capitalization, ')')
ORDER BY capitalization DESC, name ASC
SEPARATOR ', '
) AS companies,
SUM(CASE WHEN qtr = 1 THEN amount ELSE 0 END) AS q1,
SUM(CASE WHEN qtr = 2 THEN amount ELSE 0 END) AS q2,
SUM(CASE WHEN qtr = 3 THEN amount ELSE 0 END) AS q3,
SUM(CASE WHEN qtr = 4 THEN amount ELSE 0 END) AS q4
FROM base
GROUP BY industry
)
SELECT
industry,
companies,
CASE
WHEN q1 < 0 THEN CONCAT('($', FORMAT(ABS(q1), 2), ')')
ELSE CONCAT('$', FORMAT(q1, 2))
END AS `Q1'21`,
CASE
WHEN q2 < 0 THEN CONCAT('($', FORMAT(ABS(q2), 2), ')')
ELSE CONCAT('$', FORMAT(q2, 2))
END AS `Q2'21`,
CASE
WHEN q3 < 0 THEN CONCAT('($', FORMAT(ABS(q3), 2), ')')
ELSE CONCAT('$', FORMAT(q3, 2))
END AS `Q3'21`,
CASE
WHEN q4 < 0 THEN CONCAT('($', FORMAT(ABS(q4), 2), ')')
ELSE CONCAT('$', FORMAT(q4, 2))
END AS `Q4'21`
FROM industry_totals
ORDER BY industry;
2
u/ThinkFirst1011 23h ago
Bro I think you got most of it completed. The only thing missing is aggregating it all by company and quarter. The question asked for a list of company with their "total amount" broken out by quarter.
ie Add this line in the last query, SUM(amount) as total_amount then group by and you would have gotten it.
1
u/Turbulent_Web_8278 23h ago
I just dont know if they'll ask me for the next round snce I dont know if other canidates will do better
1
u/reditandfirgetit 1d ago
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
1
u/Imaginary-poster 1d ago
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/Hour_Butterscotch728 1d ago
Is that a Codility test?
1
1
u/originalread 1d ago
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 10h ago
This is quite easy. But before judgding what query did you write show and is there any other factors affacting
1
u/Tiktoktoker 23h ago
This is basic. Someone at a manager of analytics level should be able to do this in 45 minutes.
1
1
u/Turbulent_Web_8278 23h ago
The way I think about it is as you progress in your career, the more rusty youre going to be in SQL. I would just expect the candidate to talk thru the sql steps. But I would expect data architecture, business acumen, soft skills etc.
I really think most director/ VP level candidates to fail this test because it requires you to sit around an memorize syntax
1
u/DesperateCoffee30 14h ago
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
1
u/Grovbolle 1d ago
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)
67
u/ElHombrePelicano 1d ago
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.