PostgreSQL: Return gaps and value mismatches between columns

Imagine the following data:

I am interested in returning the entries where there is a gap in the data for a particular student, where the category changes from one year to another, or both. So the output that I’m after is:

The reason why id 4 would not be returned is because there is actually no gap or difference between the values. Imagine the student only joined in year_2 and their last year was year_4. I’m only interested in returning gaps and/or scores different than the max score.

The code I’m using is:

with my_table (id, student, max_score, year_1, year_2, year_3, year_4, year_5)
as (values 
(1, 'Alex', 7,null, 7, 7, null, 7),
(2, 'Bruno', 10, 10, 7, 10, 9, 10),
(3, 'Charlie', 7, 7, 5, null, 7, 7),
(4, 'Dan', 3,null, 3, 3, 3, null)
),

score_check as
(select *,
     case
         when (max_score <> year_1
             or max_score <> year_2
             or max_score <> year_3
             or max_score <> year_4
             or max_score <> year_5)
         then false
         else true
     end as is_a_match
from my_table
)

select *
from score_check
where is_a_match is false

The issue with it is that it only returns me ids 2 and 3 – it is missing id 1 that has a gap (null value) in year_4.

How should I edit my code to return both gaps and values different from the max score?

Answer

Finding the gaps is the challenge here. To check for this, you can make a string from the values and a regular expression to find any digit-x-digit combinations, which you want to detect

dbfiddle.

with my_table (id, student, max_score, year_1, year_2, year_3, year_4, year_5)
as (values 
(1, 'Alex', 7,null, 7, 7, null, 7),
(2, 'Bruno', 10, 10, 7, 10, 9, 10),
(3, 'Charlie', 7, 7, 5, null, 7, 7),
(4, 'Dan', 3,null, 3, 3, 3, null)
),

score_check as
(select *, 
   case when (
     --Check if there is a gap by making a concatenating
     --the values for all years, replacing nulls with an 
     --'x'. Use a regular expression to check if it matches
     --a pattern looking like digit-character-digit
     coalesce(year_1::character, 'x')||
     coalesce(year_2::character, 'x')||
     coalesce(year_3::character, 'x')||
     coalesce(year_4::character, 'x')||
     coalesce(year_5::character, 'x') ~ 'd+xd+') 
     --Check if all scores are equal to the max_score
     or
     (
     year_1 <> max_score or
     year_2 <> max_score or
     year_3 <> max_score or
     year_4 <> max_score or
     year_5 <> max_score 
     )
     then false else true end is_a_match
from my_table
)

select *
from score_check
where is_a_match is false