I wrote the code to solve this task, but I get a different answer than what the code in the hint produces.
Now I wonder whether the hint is the solution code, or if it’s meant to be a starting point, with additional code expected.
I’m including the description of the task, and the code in the hint.
The “hint code” produces 3 records, for personality type ISTJ.
My code produces 6 records
– 3 for ISTJ (matches results of hint code)
– 3 for ENFJ (doesn’t come from hint code)
I think that my answer is correct because, for each of the 2 personality types, there are 3 projects (it’s a tie!), and no other personality type has more than 3 associated projects.
But as I said, I don’t know if the hint code was meant to obtain the complete result set, or if
more code was expected to obtain the additional 3 records
Analyze Data with SQL - VR Startup Company - Task 11
What is the code that you wrote? Did you use LIMIT 1
?
I ask about LIMIT 1
b/c I interpreted the question as, ‘find the most represented personality type’, which to me, means only 1 personality type.
I think the hint is the actual code to achieve the answer.
As a newbie, my code is long and winding, with several CTEs, but I eventually got the logic.
How I can improve the efficiency of my logic is for another time, but I was looking to
get the correct answer first and foremost.
I realized early on that I couldn’t merely use a LIMIT 1
because there was a tie.
Two different personality types were each associated with 3 projects.
I could say LIMIT 2
, but that’s like hardcoding a solution, because an actual
problem would have an unknown number of ties.
To be dynamic about it, I used the MAX() function to determine the largest # of projects associated with a
personality type, and that the value of MAX() = 3.
But it gets more complex because two distinct personality types each has 3 projects.
WITH apwp AS (
-- apwp = all personalities with projects
SELECT personality, COUNT(*) as num_projects
FROM employees
WHERE current_project IS NOT NULL
GROUP BY personality
),
max_projects AS (
-- maximum # of projects for a personality type
-- Evaluates to 3
SELECT MAX(num_projects) as maximum
FROM apwp
),
mrp AS (
-- Retrieves ENFJ and ISTJ
-- Each personality type has 3 associated projects
SELECT personality FROM apwp
WHERE num_projects =
(SELECT *
FROM max_projects
)
)
SELECT last_name, first_name, personality, project_name FROM employees
JOIN
projects
ON employees.current_project = projects.project_id
WHERE personality IN
(SELECT * FROM mrp)
AND current_project IS NOT NULL
ORDER BY personality;
To conclude, my interpretation differs from yours. It’s true that the task asks for a singular personality type, but there are two personality types that each have 3 associated projects. Without a tiebreaker criterion, I can’t say that the 3 records for ISTJ are valid, and the 3 records for ENFJ are invalid.
By the way, thank you very much for even responding. You’re exceptionally generous with your time.,
2 Likes