title: Codility PostgreSQL Question: Aggregation and conditionality in SQL query. Completely Stuck
body: <p>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 <em>close</em> to the desired return from the query but can not figure it out.</p>
  <p><strong>Here's the question...</strong></p>
  <p><strong>You are given two tables, tasks and reports with the following structure:</strong></p>
  <pre><code>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)
  );
  </code></pre>
  <p><strong>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.</strong></p>
  <p><strong>There are three difficulties:</strong></p>
  <p><em>&quot;Easy&quot; - If the average is higher than 60</em></p>
  <p><em>&quot;Medium&quot; - if the average is higher than 20 but lower than or equal to 60</em></p>
  <p><em>&quot;Hard&quot; - If the average score for the task is lower than or equal to 20.</em></p>
  <p><strong>Your query should return a table containing three columns:</strong></p>
  <p><strong>task_id, task_name and difficulty - the difficulty of the task, which is one of three possible strings, &quot;Easy&quot; &quot;Medium&quot; or &quot;Hard&quot;. Rows should be ordered by increasing task_id. If no solutions exist for some task, that task should not appear in your table.</strong></p>
  <p>My solution (so far) for this problem is as follows:</p>
  <pre><code>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;
  </code></pre>
  <p>My Answer does everything but switch out the values of the AVG with the strings for Easy Medium and Hard. What am I missing?</p>
  <p>Linked is an image for the sample data and expected output provided by the prompt.</p>
  <p><a href="https://i.stack.imgur.com/rCezO.png" rel="nofollow noreferrer">The Image has example data and an expected output</a></p>