Question 221332 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL join and populate new table with a new column and values based on other columns

I am trying some practice SQL problems and I am having trouble with one question.

The question wants me to return a table that has [Test ID, Test Name, and Test Difficulty]. The issue is that I have to create the [Test Difficulty] column based on data between two tables (participant scores are in table A and test name is in table B). The tables share test id.

In short, I need to join the two tables, then average scores, and then create the new test difficulty column and populate it based on the average scores (e.g., if average score is less than 20, then test difficulty = "hard").

Currently, I have managed to join the tables and average the scores but I am having trouble with the last part of creating this new column and populating based on the conditional. Can anyone help?

Note, creating new table is not an option, all should be done through the query execution area.

Code below:

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

Table B

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

Values in each table

insert into reports values (13, 101, 'John Smith', 100);
insert into reports values (24, 123, 'Delaney Lloyd', 34);
insert into reports values (37, 300, 'Monroe Jimenez', 50);
insert into reports values (49, 101, 'Stanley Price', 45);
insert into reports values (51, 142, 'Tanner Sears', 37);
insert into reports values (68, 142, 'Lara Fraser', 3);
insert into reports values (83, 300, 'Tanner Sears', 0);

insert into tasks values (101, 'MinDist');
insert into tasks values (123, 'Equi');
insert into tasks values (142, 'Median');
insert into tasks values (300, 'Tricoloring');

My code:

select * 
from
(
select reports.task_id, 
tasks.name,
avg(reports.score) as average
from reports
join tasks
on tasks.id = reports.task_id
group by reports.task_id, tasks.name
) practice;
Tags:

2

Answers


  1. 0

    One option (not the only one) is to put the existing query you have into a common table expression and join that to your update statement. It would look something like:

    alter table tasks add column "Test Difficulty" varchar(40);
with cte as
 (select reports.task_id, 
 tasks.name,
 avg(reports.score) as average
 from reports
 join tasks
 on tasks.id = reports.task_id
 group by reports.task_id, tasks.name)
update tasks
set "Test Difficulty" =
 CASE
  WHEN cte.average <= 20.0 THEN 'Hard'
  WHEN cte.average <= 40.0 THEN 'Medium'
  ELSE 'Easy'
 END
from cte
WHERE cte.task_id = tasks.id

    You can see it working here.

    The with cte as (...) just wraps your existing query. The update tasks set ... from cte where cte.task_id = tasks.id joins your query to the table you want to update.

    The case statement makes the decision about the text you want to populate. I made up some numbers/text since you didn’t fully specify the requirement, but the basic idea should apply to your actual problem.

    Another option might involve doing the calculation in a subquery. Something like this.

    Login or Signup to reply.
  2. 0

    It’s a bit unclear whether you want to insert your results somewhere, but here’s something that’s as close to the query you have as you can get:

    select
    practice.*,
    case
        when practice.average <= 20 then 'HARD'
        when practice.average > 20 and practice.average <= 40 then 'MEDIUM'
        else 'EASY'
    end as test_difficulty
from (
    select
        reports.task_id, 
        tasks.name,
        avg(reports.score) as average
    from reports
    join tasks on tasks.id = reports.task_id
    group by reports.task_id, tasks.name
) practice;

    You can, of course, alter the CASE statement as needed. I’m using "practice.average > 20 and practice.average <= 40" instead of BETWEEN, because BETWEEN is inclusive; this way each difficulty starts on .0(…)01 and ends on 0.

    There are of course many other ways to do it – CTEs, functions, etc. – but this seems closest to what you’re describing.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search