SELECT tx_id, ARRAY_AGG(PUBKEY_SCRIPT_ADDRESS) AS addresses

FROM bitcoin.core.fact_inputs

where date_trunc('d', block_timestamp) = '2024-04-10'

GROUP BY tx_id

)

SELECT LISTAGG(tx_id, ',') WITHIN GROUP (ORDER BY tx_id) AS cluster_id, f.address AS address

FROM ClusterAddresses

CROSS JOIN LATERAL FLATTEN(input => addresses) AS f

GROUP BY f.address