Unnest ids from arrays based on priority with no duplication
I've solved this relationaly by unnesting and assigning the weights as suggested by @Akina.
with weighted as (
  select
    case priority
      when 'High' then 6
      when 'Medium' then 3
      when 'Low' then 0
    end as priority,
    productive,
    unproductive,
    neutral
  from
    data
), unnested as (
  select
    unnest(productive) as id, priority, 2 as category 
  from
    weighted
  union all
  select
    unnest(unproductive) as id, priority, 1 as category 
  from
    weighted
  union all
  select
    unnest(neutral) as id, priority, 0 as category
  from
    weighted
) 
select
  case category
    when 0 then 'Neutral'
    when 1 then 'UnProductive'
    when 2 then 'Productive'
  end,
  array_agg(id)
from unnested x
where not exists (
  select null
  from unnested
  where id = x.id
  and priority + category &gt; x.priority + x.category
)
group by category, 1
order by category desc;

See https://dbfiddle.uk/?rdbms=postgres_13&amp;fiddle=8a54e1f8018c49146faba3e446f7b694
It can probably be done more efficiently.