Cardano transactions between stake pool owners

pool_owners_txs.csv

pool_owners_txs.sql

\copy (select ticker_in,hash_in,ticker_out,hash_out,round(sum(amount)) from
(select distinct on(tx.id) tx.id,tx.hash,pool_offline_data_in.ticker_name ticker_in, encode(pool_hash_in.hash_raw,'hex') hash_in,pool_offline_data_out.ticker_name ticker_out,encode(pool_hash_out.hash_raw,'hex') hash_out,tx_out.value/1000000 amount
from tx_out
inner join tx on tx.id=tx_out.tx_id
inner join tx_in on tx_in.tx_in_id=tx_out.tx_id
inner join tx_out tx_out_in on tx_in.tx_out_id=tx_out_in.tx_id and tx_in.tx_out_index=tx_out_in.index
inner join pool_owner pool_owner_out on pool_owner_out.addr_id=tx_out.stake_address_id
inner join pool_update pool_update_out on pool_update_out.id=pool_owner_out.pool_update_id
inner join pool_owner pool_owner_in on pool_owner_in.addr_id=tx_out_in.stake_address_id
inner join pool_update pool_update_in on pool_update_in.id=pool_owner_in.pool_update_id
inner join pool_hash pool_hash_out on pool_hash_out.id=pool_update_out.hash_id
inner join pool_hash pool_hash_in on pool_hash_in.id=pool_update_in.hash_id
inner join pool_offline_data pool_offline_data_out on pool_offline_data_out.pool_id=pool_hash_out.id
inner join pool_offline_data pool_offline_data_in on pool_offline_data_in.pool_id=pool_hash_in.id
where pool_hash_in.id != pool_hash_out.id
and substring(pool_offline_data_in.ticker_name,1,3) != substring(pool_offline_data_out.ticker_name,1,3)
order by tx.id desc, pool_offline_data_in.id desc, pool_offline_data_out.id desc, tx_out.value desc
) s 
group by (ticker_in, hash_in,ticker_out,hash_out)
order by sum(amount) desc) to '/tmp/cardano_pool_owner_txs.csv' csv header;