SQLize Online / PHPize Online  /  SQLtest Online

create table wo_order (id int, recipient text); create table wo_order_flow (id int, order_id int, tx_id int); create table io_order (id int, payer_source text); create table io_order_flow (id int, order_id int, tx_id int); create table txs (id int, hash text); insert into txs values (1, 'hash_one'); insert into txs values (2, 'hash_two'); insert into txs values (3, 'hash_three'); insert into txs values (4, 'hash_four'); insert into txs values (5, 'hash_five'); insert into txs values (6, 'hash_six'); insert into wo_order values (1, '1234'); insert into wo_order values (2, '5678'); insert into wo_order values (3, '1234'); insert into wo_order_flow values (1, 1, 1); insert into wo_order_flow values (2, 1, 2); insert into wo_order_flow values (3, 2, 3); insert into wo_order_flow values (5, 2, 1); insert into wo_order_flow values (6, 3, 1); insert into io_order values (1, '1234'); insert into io_order values (2, '5678'); insert into io_order values (3, '1234'); insert into io_order values (4, '3333'); insert into io_order_flow values (1, 1, 4); insert into io_order_flow values (2, 1, 4); insert into io_order_flow values (3, 2, 5); insert into io_order_flow values (5, 2, 5); insert into io_order_flow values (6, 3, 6); insert into io_order_flow values (6, 4, 1); create or replace function array_unique(arr anyarray) returns anyarray as $body$ select array( select distinct unnest($1) ) $body$ language 'sql'; WITH cte_io AS ( SELECT array_agg(DISTINCT io.payer_source) AS io_unique, iof.tx_id AS tx_id FROM io_order_flow iof LEFT JOIN io_order io ON iof.order_id = io.id WHERE iof.tx_id IN (1, 2, 3, 4, 5, 6) AND io.payer_source NOTNULL GROUP BY iof.tx_id ), cte_wo AS ( SELECT array_agg(DISTINCT wo.recipient) AS wo_unique, wof.tx_id AS tx_id FROM wo_order_flow wof LEFT JOIN wo_order wo ON wof.order_id = wo.id WHERE wof.tx_id IN (1, 2, 3, 4, 5, 6) GROUP BY wof.tx_id ) SELECT t.id "txID", cw.wo_unique "woUnique", ci.io_unique "ioUnique", array_unique(cw.wo_unique || ci.io_unique) "uniq" FROM txs t LEFT JOIN cte_wo cw ON cw.tx_id = t.id LEFT JOIN cte_io ci ON ci.tx_id = t.id WHERE t.id IN (1, 2, 3, 4, 5, 6) GROUP BY t.id, cw.wo_unique, ci.io_unique