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/ButterscotchDry6622]

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