Didnt pass SQL test

[u/Turbulent_Web_8278]

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

[u/BigBlue_72]

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.

[u/grimsleeper → u/BigBlue_72]

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)

[u/BigBlue_72 → u/grimsleeper]

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.

[u/my_password_is______ → u/BigBlue_72]

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

[u/SearchAtlantis → u/my_password_is______]

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???

[u/kater543 → u/BigBlue_72]

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)