PostgreSQL SQL Exercises

This is the task difficulty rating problem — a common one in Codility hiring assessments.

The problem

You're given two tables, tasks (id, name) and reports (id, task_id, candidate, score), where each report contains a score from 0 to 100. Write a query that assigns a difficulty to each task that has at least one solution, based on the average score of all submitted solutions — including multiple submissions from the same candidate.

The rules:

The output should have three columns — task_id, task_name, and difficulty — ordered by increasing task_id, and tasks with no solutions shouldn't appear at all.

-- Schema, for reference:
CREATE TABLE tasks (
    id   INTEGER PRIMARY KEY,  -- unique task id
    name VARCHAR(30) NOT NULL  -- human-readable task name
);

CREATE TABLE reports (
    id        INTEGER PRIMARY KEY,           -- unique report (submission) id
    task_id   INTEGER REFERENCES tasks (id), -- which task was attempted
    candidate VARCHAR(30) NOT NULL,          -- who submitted; the SAME candidate
                                             -- can appear many times per task
    score     INTEGER NOT NULL               -- 0..100, awarded for this submission
);

The solution

The classic solution shape is an INNER JOIN (which naturally drops tasks with no reports), a GROUP BY on the task, and a CASE expression over AVG(score):

SELECT
    t.id   AS task_id,    -- the task's primary key → first output column
    t.name AS task_name,  -- human-readable name   → second output column

    CASE  -- bucket the per-task average score into a difficulty label.
          -- AVG(r.score) is an aggregate: it is computed ONCE PER GROUP
          -- (i.e. once per task, thanks to the GROUP BY below), and it
          -- silently ignores NULL scores (none exist here — NOT NULL).

        -- CASE arms are evaluated TOP TO BOTTOM and the first match wins,
        -- so ordering the thresholds ascending keeps every arm a simple
        -- one-sided comparison — no need to write "BETWEEN 21 AND 60".

        WHEN AVG(r.score) <= 20 THEN 'Hard'    -- boundary: exactly 20 is Hard
                                               -- (<=, not <  — easy to flip!)

        WHEN AVG(r.score) <= 60 THEN 'Medium'  -- only reached when avg > 20,
                                               -- so this arm really means
                                               -- 20 < avg <= 60; exactly 60
                                               -- is Medium.

        ELSE 'Easy'                            -- everything above 60.
                                               -- (Also where a NULL average
                                               -- would land — see gotcha #2.)
    END AS difficulty

FROM tasks t

-- INNER JOIN, not LEFT JOIN: a task with ZERO reports produces no joined
-- rows at all, so it simply vanishes from the result — which is exactly
-- what "tasks with no solutions shouldn't appear" asks for. No HAVING
-- COUNT(*) > 0 needed; the join does the filtering for free.
JOIN reports r
  ON r.task_id = t.id   -- match every submission to its task

-- Collapse the joined rows to ONE output row per task. Every report row
-- for that task feeds AVG() — multiple submissions by the same candidate
-- are deliberately NOT deduplicated; the statement says ALL submitted
-- solutions count.
-- (t.name rides along in the GROUP BY because it appears un-aggregated in
-- the SELECT; in PostgreSQL `GROUP BY t.id` alone would also be legal,
-- since t.id is the primary key and t.name is functionally dependent on it.)
GROUP BY t.id, t.name

ORDER BY t.id;  -- increasing task_id, as required

The gotchas

1. Integer division / truncated averages. Some engines truncate AVG over integer columns — SQL Server returns an int, so an average of 20.5 becomes 20 and gets misfiled as Hard. Casting fixes it:

-- Portable across engines: force a decimal average before comparing.
WHEN AVG(CAST(r.score AS DECIMAL)) <= 20 THEN 'Hard'

PostgreSQL is safe here — AVG(integer) returns numeric — but it's worth knowing which engine the assessment runs on.

2. Accidentally using a LEFT JOIN. A LEFT JOIN keeps tasks with no reports, with AVG(r.score) evaluating to NULL for them. NULL <= 20 and NULL <= 60 are both unknown (not true), so those rows fall through to the ELSE arm and show up as bogus 'Easy' tasks — violating the "don't appear at all" requirement twice over.

3. Boundary conditions. Exactly 20 must be Hard and exactly 60 must be Medium. Writing < 20 / < 60 (or inverting to >= 20 THEN 'Medium') shifts the boundaries by one representable value and fails the hidden edge-case tests — the easiest points to lose on this problem.

How the engine actually runs it

The query reads top-to-bottom, but PostgreSQL evaluates it in a different logical order — and most of the classic mistakes on this problem come from forgetting that order:

1. FROM + JOIN — build the working set: every reports row glued to its tasks row. A task with zero reports contributes zero rows here, which is why it later disappears.
2. WHERE — (none here) would filter individual joined rows, before any grouping. This is why you can't write WHERE AVG(r.score) <= 20 — no groups exist yet.
3. GROUP BY — collapse the surviving rows into one group per (t.id, t.name).
4. Aggregates — AVG(r.score) is computed once per group.
5. SELECT / CASE — only now are output expressions evaluated, so CASE can see the per-group average.
6. ORDER BY — sorts the final rows; it can see SELECT aliases, the only clause that can.

Two practical consequences:

• A filter on the average belongs in HAVING (runs after step 4), never WHERE.
• You can't reference the difficulty alias inside another SELECT expression in the same query level — aliases don't exist until step 5 finishes. If you want to reuse the average, compute it one level down (see the CTE variant below).

A worked trace

Take this tiny dataset:

tasks                    reports
id | name                id | task_id | candidate | score
---+------               ---+---------+-----------+------
 1 | fizzbuzz             1 |    1    | ana       |  90
 2 | regex-golf           2 |    1    | ana       |  30   ← same candidate, counted again
 3 | dijkstra             3 |    2    | bob       |  60
                          4 |    2    | cat       |  20

Step 1 — the join. Each report finds its task; dijkstra (no reports) produces nothing:

t.id | t.name     | r.score
-----+------------+--------
  1  | fizzbuzz   |   90
  1  | fizzbuzz   |   30
  2  | regex-golf |   60
  2  | regex-golf |   20

Step 2 — the groups and their averages. AVG runs once per task: fizzbuzz → (90+30)/2 = 60, regex-golf → (60+20)/2 = 40. Note ana's two submissions both count — the problem says all submissions, so no DISTINCT.

Step 3 — the CASE. 60 is not ≤ 20, but it is ≤ 60 → Medium (the boundary lands on the second arm). 40 → Medium as well:

task_id | task_name  | difficulty
--------+------------+-----------
   1    | fizzbuzz   | Medium     ← avg exactly 60: Medium, not Easy
   2    | regex-golf | Medium

dijkstra never appears — it was gone before grouping even started. That single trace exercises both gotcha #2 (the join does the filtering) and gotcha #3 (the ≤ 60 boundary).

Cleaner variants

Compute the average once, name it, then bucket it. Repeating AVG(r.score) in every CASE arm works, but a derived table (or CTE) reads better and gives the optimizer the same plan:

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

The outer query runs at step 5 of its own level, so it may freely reference avg_score — the aliasing problem from the execution-order section dissolves.

GROUP BY t.id alone is legal in PostgreSQL. Because t.id is the primary key, t.name is functionally dependent on it, and the SQL standard (which PostgreSQL implements here) allows omitting it. MySQL with ONLY_FULL_GROUP_BY disabled allows far sloppier versions — and silently picks arbitrary values — so spelling out GROUP BY t.id, t.name is the portable habit.

Why not a window function? AVG(score) OVER (PARTITION BY task_id) computes the same average but keeps every report row — you'd then need DISTINCT to collapse them back. Window functions answer "show each row alongside its group's aggregate"; this problem wants "one row per group", which is exactly what GROUP BY is for. Knowing when not to reach for a window function is itself a common interview probe.

Performance notes

For an assessment-sized dataset none of this matters, but interviewers often follow up with "and at 100M reports?":

• The plan you'd expect from EXPLAIN is a sequential scan of reports feeding a hash join against tasks, then a HashAggregate for the GROUP BY, then a sort for ORDER BY t.id. There's no WHERE, so every report is read regardless — indexes can't reduce the row count here.
• An index on reports(task_id) doesn't help this exact query much, but it's the index you'd want anyway: the foreign key gets no index automatically in PostgreSQL, and without it every delete on tasks scans reports.
• A covering index reports(task_id, score) lets PostgreSQL satisfy the join + aggregate from an index-only scan — the kind of detail that turns a pass into a strong pass.
• If the rating were served on a hot path, you'd precompute it: a materialized view over this exact query, refreshed on a schedule, is the textbook answer.

Did your solution look similar, or did you take a different route?