For an online platform count the number of students with perfect score-CodePudding

I recently gave test on codility platform and was stuck on below SQL question.

Question: For an online platform assessments were conducted for 3 topics and scores were provided.

Table: Assessments

Input:

|Id|experience|sql|algo|bug_fixing|
|1 |5         |100|null|100       |
|2 |1         |100|100 |null      |
|3 |3         |100|null|50        |
|4 |5         |100|null|50        |
|5 |5         |100|100 |100       |

We need to print experience, count of students having a perfect score(null is considered a perfect score) as max and count of students with that year of experience as counts. Results to be printed in descending order of years of experience.

Output:

|experience|max|counts|
|5         |2  |3     |
|3         |0  |1     |
|1         |1  |1     |

My solution:

With t1 as
(select experience, 
 count(experience) as max 
 from assessments 
 where (sql=100 or sql=null) 
 and (algo=100 or algo=null) 
 and (bug_fixing=100 or bug_fixing=null) 
group by experience) 

select a.experience,
t1.max, 
count(a.experience) as counts 
from assessments a join t1 on a.experience=t1.experience 
group by a.experience

However, I am getting incorrect count in output for second column(max).

Can someone tell me error or correction needed in my code? TIA.

CodePudding user response：

Answer:

With t1 as
(select experience, 
 count(experience) as max 
 from assessment 
 where (sql=100 or sql is null) 
 and (algo=100 or algo is null) 
 and (bug_fixing=100 or bug_fixing is null) 
group by experience) 

select a.experience,
coalesce(t1.max,0), 
count(a.experience) as counts 
from assessment a left join t1 on a.experience=t1.experience 
group by a.experience,t1.max;

CodePudding user response：

You do not need subqueries or with statements. Use the aggregate with filter option, e.g.:

select 
    experience,
    count(*) filter (where coalesce(sql, 100)  coalesce(algo, 100)  coalesce(bug_fixing, 100) = 300) as max,
    count(*)
from assessments
group by experience
order by experience desc

Test it in db<>fiddle.