Possible issue with the hint for Analyze Data with SQL - VR Startup Company -Task 11

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. :thinking:

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