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?

CodePudding user response：

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 x\d ') 
     --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

CodePudding user response：

You can generalize this to deal with more than just the five years for the dummies on the six-year plan:

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)
), unpivot1 as (
  select to_jsonb(my_table) as j
    from my_table
), unpivot2 as (
  select (j->>'id')::int as id, j->>'max_score' as max_score, k, v,
         replace(k, 'year_', '')::int as yearno
    from unpivot1
         cross join lateral jsonb_each_text(j) as e(k, v)
   where k like 'year\_%'
), summary as (
  select id,
         string_agg(
           case v = max_score
             when true then 't'
             when false then 'f'
             else 'n'
           end,
           ''
         ) as actual
    from unpivot2
   group by id
)
select t.*
  from summary s
       join my_table t on t.id = s.id
 where trim(s.actual, 'n') ~ '[fn]'
;

db<>fiddle here