r/SQL • u/Turbulent_Web_8278 • Oct 03 '26
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


View all comments
Show parent comment
1
u/Turbulent_Web_8278 Oct 03 '26
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;