Query editor
with eth_socket_tx AS (SELECT tx_hash, block_number FROM ethereum.logswhere contract_address in(0x3a23F943181408EAC424116Af7b7790c94Cb97a5,0xc30141B657f4216252dc59Af2e7CdB9D8792e1B0)AND topic0 in(0x74594da9e31ee4068e17809037db37db496702bf7d8d63afe6f97949277d1609, 0x28fd8a5dda29b4035905e0657f97244a0e0bef97951e248ed0f2c6878d6590c2)),avax_socket_tx AS (SELECT tx_hash, block_number FROM avalanche_c.logswhere contract_address in(0x3a23F943181408EAC424116Af7b7790c94Cb97a5,0xc30141B657f4216252dc59Af2e7CdB9D8792e1B0)AND topic0 in(0x74594da9e31ee4068e17809037db37db496702bf7d8d63afe6f97949277d1609, 0x28fd8a5dda29b4035905e0657f97244a0e0bef97951e248ed0f2c6878d6590c2)),bnb_socket_tx AS (SELECT tx_hash, block_number FROM bnb.logswhere contract_address in(0x3a23F943181408EAC424116Af7b7790c94Cb97a5,0xc30141B657f4216252dc59Af2e7CdB9D8792e1B0)AND topic0 in(0x74594da9e31ee4068e17809037db37db496702bf7d8d63afe6f97949277d1609, 0x28fd8a5dda29b4035905e0657f97244a0e0bef97951e248ed0f2c6878d6590c2)),polygon_socket_tx AS (SELECT tx_hash, block_number FROM polygon.logswhere contract_address in(0x3a23F943181408EAC424116Af7b7790c94Cb97a5,0xc30141B657f4216252dc59Af2e7CdB9D8792e1B0)AND topic0 in(0x74594da9e31ee4068e17809037db37db496702bf7d8d63afe6f97949277d1609, 0x28fd8a5dda29b4035905e0657f97244a0e0bef97951e248ed0f2c6878d6590c2)),arb_socket_tx AS (SELECT tx_hash, block_number FROM arbitrum.logswhere contract_address in(0x3a23F943181408EAC424116Af7b7790c94Cb97a5,0xc30141B657f4216252dc59Af2e7CdB9D8792e1B0)AND topic0 in(0x74594da9e31ee4068e17809037db37db496702bf7d8d63afe6f97949277d1609, 0x28fd8a5dda29b4035905e0657f97244a0e0bef97951e248ed0f2c6878d6590c2)),opt_socket_tx AS (SELECT tx_hash, block_number FROM optimism.logswhere contract_address in(0x3a23F943181408EAC424116Af7b7790c94Cb97a5,0xc30141B657f4216252dc59Af2e7CdB9D8792e1B0)AND topic0 in(0x74594da9e31ee4068e17809037db37db496702bf7d8d63afe6f97949277d1609, 0x28fd8a5dda29b4035905e0657f97244a0e0bef97951e248ed0f2c6878d6590c2)),eth as (select date_trunc('day', t2.block_time) as day,case when t1.tx_hash is not null then 'Socket' else 'Lifi' end as label,t2."from" as userfrom eth_socket_tx t1right join ethereum.transactions t2 on t1.tx_hash=t2.hash and t1.block_number=t2.block_numberwhere t2.to=0x82E0b8cDD80Af5930c4452c684E71c861148Ec8A),avax as (select date_trunc('day', t2.block_time) as day,case when t1.tx_hash is not null then 'Socket' else 'Lifi' end as label,t2."from" as userfrom avax_socket_tx t1right join avalanche_c.transactions t2 on t1.tx_hash=t2.hash and t1.block_number=t2.block_numberwhere t2.to=0x82E0b8cDD80Af5930c4452c684E71c861148Ec8A),bnb as (select date_trunc('day', t2.block_time) as day,case when t1.tx_hash is not null then 'Socket' else 'Lifi' end as label,t2."from" as user