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

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.

Solution

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.