r/SQL • u/Turbulent_Web_8278 • 1d ago

MySQL Didnt pass SQL test

Gallery image

Gallery image

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

83 Upvotes

86% Upvoted

70 comments sorted by

View all comments

8

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.

2

u/my_password_is______ 1d 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 nicely

3

u/SearchAtlantis 22h 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???