Query editor
WITH bridge_txns AS (SELECTcontract_address AS bridge_contract_address,evt_tx_hash,evt_index,evt_block_time,evt_block_number,chain,bridgeDataFROM lifi_multichain.lifidiamond_v2_evt_lifitransferstartedWHERE json_extract_scalar(bridgeData, '$.integrator') = 'cryptex-finance'UNIONSELECT contract_address AS bridge_contract_address,evt_tx_hash,evt_index,evt_block_time,evt_block_number,chain,bridgeDataFROM lifi_multichain.lifidiamond_evt_lifitransferstartedWHERE json_extract_scalar(bridgeData, '$.integrator') = 'cryptex-finance'),bridge_txns_ext AS (SELECTbridge_contract_address,evt_tx_hash,evt_index,evt_block_time,evt_block_number,chain,json_extract_scalar(bridgeData, '$.transactionId') AS transaction_id,json_extract_scalar(bridgeData, '$.bridge') AS bridge,json_extract_scalar(bridgeData, '$.integrator') AS integrator,json_extract_scalar(bridgeData, '$.referrer') AS referrer,json_extract_scalar(bridgeData, '$.sendingAssetId') AS sending_asset_id,json_extract_scalar(bridgeData, '$.receiver') AS receiver,CAST(json_extract_scalar(bridgeData, '$.minAmount') AS DECIMAL) AS min_amount,CAST(json_extract_scalar(bridgeData, '$.destinationChainId') AS BIGINT) AS destination_chain_id,json_extract_scalar(bridgeData, '$.hasSourceSwaps') AS has_source_swaps,json_extract_scalar(bridgeData, '$.hasDestinationCall') AS has_destination_callFROM bridge_txns)SELECT *,DATE_TRUNC('day', evt_block_time) AS day,CASEWHEN min_amount IS NOT NULL AND decimals IS NOT NULL THEN min_amount / POW(10, decimals)ELSE min_amount / 1e18END AS token_value,CASEWHEN destination_chain_id = 1399811149 THEN 'Solana'WHEN destination_chain_id = 7777777 THEN 'Zora'WHEN destination_chain_id = 534352 THEN 'Scroll'WHEN destination_chain_id = 59144 THEN 'Linea'WHEN destination_chain_id = 43114 THEN 'Avalanche C-Chain'WHEN destination_chain_id = 42220 THEN 'Celo Mainnet'WHEN destination_chain_id = 42161 THEN 'Arbitrum One'WHEN destination_chain_id = 8453 THEN 'Base'WHEN destination_chain_id = 7000 THEN 'ZetaChain Mainnet'WHEN destination_chain_id = 1101 THEN 'Polygon zkEVM'WHEN destination_chain_id = 1088 THEN 'Metis Andromeda Mainnet'WHEN destination_chain_id = 324 THEN 'zkSync Mainnet'WHEN destination_chain_id = 169 THEN 'Manta Pacific Mainnet'WHEN destination_chain_id = 137 THEN 'Polygon Mainnet'WHEN destination_chain_id = 100 THEN 'Gnosis'WHEN destination_chain_id = 56 THEN 'BNB Smart Chain Mainnet'WHEN destination_chain_id = 10 THEN 'OP Mainnet'WHEN destination_chain_id = 1 THEN 'Etheruem'