Array intersection as aggregate function for group by, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

drop table if exists your_table;

create table your_table(
  name text,
  preferences text[]
);

insert into your_table(name, preferences)
values ('John', ARRAY['pizza', 'meat']),
       ('John', ARRAY['pizza', 'spaghetti']),
       ('Bill', ARRAY['lettuce', 'pizza']),
       ('Bill', ARRAY['tomatoes']);

with recursive cte(name, pref_intersect, pref_prev, iteration) as (
    select   name,
             min(preferences),
             min(preferences),
             0
    from     your_table
    group by name
  union all
    select   name,
             array(select e from unnest(pref_intersect) e
                   intersect
                   select e from unnest(pref_next) e),
             pref_next,
             iteration + 1
    from     cte,
    lateral  (select   your_table.preferences pref_next
              from     your_table
              where    your_table.name        = cte.name
              and      your_table.preferences > cte.pref_prev
              order by your_table.preferences
              limit    1) n
)
select   distinct on (name) name, pref_intersect::text
from     cte
order by name, iteration desc;

insert into your_table(name, preferences)
values ('John', ARRAY['meat', 'spaghetti']);


absolute service time: 0,51 sec