By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
drop table if exists mytable1;
create table mytable1(row_number int primary key, list_of_ids int[]);
insert into mytable1 values
(1, '{633681,1278392,2320888,2200426}'),
(2, '{2443842,2959599,3703823,3330376,915750,941736}');
drop table if exists mytable2;
create table mytable2(id int primary key, company text);
insert into mytable2 values
(633681, 'apple'),
(1278392, 'charmander'),
(2320888, 'apple'),
(2200426, null),
(2443842, 'batman');
DROP TABLE
CREATE TABLE
INSERT 0 2
DROP TABLE
CREATE TABLE
INSERT 0 5
select
row_number,
list_of_ids,
string_agg(format('%s %s', count, company), ', ' order by count desc, company)
from (
select
row_number,
list_of_ids,
coalesce(company, '<null>') as company,
count(*)
from (
select row_number, list_of_ids, unnest(list_of_ids) as id
from mytable1
) t1
join mytable2 t2 using(id)
group by 1, 2, 3
) s
group by 1, 2
| row_number | list_of_ids | string_agg |
|---|---|---|
| 1 | {633681,1278392,2320888,2200426} | 2 apple, 1 charmander, 1 <null> |
| 2 | {2443842,2959599,3703823,3330376,915750,941736} | 1 batman |
SELECT 2