Codility PostgreSQL Question: Aggregation and conditionality in SQL query. Completely Stuck

Question

I have been applying for Junior DEV roles and encountered a problem on a Codility take home test that I aced... except for this question. Which I bombed. I have done research and have gotten close to the desired return from the query but can not figure it out.

Here's the question...

You are given two tables, tasks and reports with the following structure:

create table tasks (
id integer not null,
name varchar(40) not null,
unique(id)
);

create table reports(
id integer not null,
task_id integer not null,
candidate varchar(40) not null,
score integer not null,
unique(id)
);

Your task is to write an SQL which assigns a difficulty rating to each task having at least one solution. The difficulty of the task depends on the average score of all candidates' solutions submitted for this task. It is possible that one candidate have submitted multiple solutions for the same task; in this case, all of those solutions should be included in the average.

There are three difficulties:

"Easy" - If the average is higher than 60

"Medium" - if the average is higher than 20 but lower than or equal to 60

"Hard" - If the average score for the task is lower than or equal to 20.

Your query should return a table containing three columns:

task_id, task_name and difficulty - the difficulty of the task, which is one of three possible strings, "Easy" "Medium" or "Hard". Rows should be ordered by increasing task_id. If no solutions exist for some task, that task should not appear in your table.

My solution (so far) for this problem is as follows:

SELECT task_id, name,  AVG(score) AS difficulty  FROM reports AS r
JOIN tasks AS t on t.id=r.task_id
GROUP BY task_id, name
ORDER BY task_id ASC;

My Answer does everything but switch out the values of the AVG with the strings for Easy Medium and Hard. What am I missing?

Linked is an image for the sample data and expected output provided by the prompt.

The Image has example data and an expected output

Answer 1

SELECT task_id, name, 
    CASE
        WHEN AVG(score) > 60 THEN 'Easy'
        WHEN AVG(score) <= 20 THEN 'Hard'
        ELSE 'Medium'
    END
 AS difficulty FROM reports AS r
JOIN tasks AS t on t.id=r.task_id
GROUP BY task_id, name
ORDER BY task_id ASC;

Answer 2

SELECT task_id
    ,task_name
    ,CASE 
        WHEN average_score > 60
            THEN 'Easy'
        WHEN average_score <= 20
            THEN 'Hard'
        ELSE 'Medium'
        END AS difficulty
FROM (
    SELECT r.task_id
        ,t.name AS task_name
        ,AVG(r.score) AS average_score
    FROM reports r
    LEFT JOIN tasks t ON r.task_id = t.id
    GROUP BY 1
        ,2
    ) x
ORDER BY 1

Answer 3

As others have said you are missing a CASE statement to break the average scores into categories.

You also need to cast the score to another data type to get decimals, otherwise the output will only be integers. Not doing this will cause GameOfNuts to be labelled as Medium (AVG score of 60) instead of Easy (AVG score of 60.333) in the sample data set.

    SELECT r.task_id, t.name, 
     (CASE WHEN AVG(Cast(r.score as decimal(10,2))) > 60 THEN 'Easy'
         WHEN AVG(Cast(r.score as decimal(10,2))) BETWEEN 21 AND 60 THEN 'Medium'
         ELSE 'Hard' END
     ) AS difficulty
    FROM reports r
    JOIN tasks t
    ON r.task_id = t.id
    GROUP BY task_id, t.name
    ORDER BY task_id

Answer 4

You are missing a CASE statement. Please see below:

SELECT task_id, name,  AVG(score) AS task_avg,
 CASE 
    WHEN task_avg > 60 THEN 'Easy'
    WHEN task_avg > 20 AND task_avg <= 60 THEN 'Medium'
    WHEN task_avg <= 20 THEN 'Hard'
END AS difficulty
FROM reports AS r
JOIN tasks AS t on t.id=r.task_id
GROUP BY task_id, name
ORDER BY task_id ASC;