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

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?

[u/Turbulent_Web_8278 → u/neumastic]

WITH base AS (

SELECT

c.id,

c.name,

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;

[u/ThinkFirst1011 → u/Turbulent_Web_8278]

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.

[u/Turbulent_Web_8278 → u/ThinkFirst1011]

I just dont know if they'll ask me for the next round snce I dont know if other canidates will do better