CREATE TABLE assets (
id serial,
name text,
owner text,
PRIMARY KEY(id)
);
CREATE TABLE users (
id serial,
username text,
ref1 text,
ref2 text,
PRIMARY KEY(id)
);
INSERT INTO assets (name, owner) VALUES
('#1', 'a'),
('#2', 'b'),
('#3', 'c'),
('#4', 'a'),
('#5', 'c'),
('#6', 'd'),
('#7', 'e'),
('#8', 'd'),
('#9', 'a'),
('#10', 'a'),
('#11', 'z')
;
INSERT INTO users (username, ref1, ref2) VALUES
('bobo', 'a', 'd'),
('toto', 'b', 'e'),
('momo', 'c', 'd'),
('lolo', 'a', 'f'),
('popo', 'c', 'f');
-- For result comparison
CREATE TABLE results (
ids int[],
usernames text[],
refs1 text[],
refs2 text[],
asset_ids int[],
asset_count int
);
INSERT INTO results (ids, usernames, refs1, refs2, asset_ids, asset_count) VALUES
(array[1,3,4,5], array['bobo', 'momo', 'lolo', 'popo'], array['a', 'c'], array['d', 'f'], array[1,3,4,5,6,8], 6),
(array[2], array['toto'], array['b'], array['e'], array[2,7], 2);
WITH agg1 as (
SELECT
string_agg(distinct users.id::text, ',') id,
string_agg(distinct username, ',') username,
string_agg(distinct ref2, ',') ref2,
string_agg(distinct assets.id::text, ',') assets_id,
sum(assets_count) assets_count,
ref1
FROM users
JOIN (
SELECT string_agg(id::text, ',') id, count(*) assets_count, owner
FROM assets GROUP BY owner
) assets ON assets.owner in (ref1, ref2)
GROUP BY ref1
) SELECT
string_to_array(string_agg(id::text, ','),',') ids,
string_to_array(string_agg(username, ','),',') usernames,
string_to_array(string_agg(ref1, ','),',') refs1,
string_to_array(ref2,',') refs2,
string_to_array(string_agg(assets_id, ','),',') assets_id,
min(assets_count) assets_count
FROM agg1
GROUP BY agg1.ref2
;
-- For comparison
SELECT * FROM results;
Telegram: DB developers channel