Query editor
WITH bridge_txns AS (SELECTchain, contract_address, evt_tx_hash, evt_tx_from, evt_tx_to,evt_tx_index, evt_index, evt_block_time, evt_block_number, evt_block_date,fromAmount, fromAssetId, integrator, receiver, referrer, toAmount, toAssetId, transactionIdFROM lifi_multichain.lifidiamond_v2_evt_lifigenericswapcompletedWHERE integrator = 'cryptex-finance'UNIONSELECTchain, contract_address, evt_tx_hash, evt_tx_from, evt_tx_to,evt_tx_index, evt_index, evt_block_time, evt_block_number, evt_block_date,fromAmount, fromAssetId, integrator, NULL AS receiver, referrer, toAmount, toAssetId, transactionIdFROM lifi_multichain.lifidiamond_evt_lifigenericswapcompletedWHERE integrator = 'cryptex-finance'),bridge_txns_ext AS (SELECTb.*,CASEWHEN b.fromAssetId = 0x0000000000000000000000000000000000000000 THEN 'WETH'ELSE t.symbolEND AS symbol_from,CASEWHEN b.toAssetId = 0x0000000000000000000000000000000000000000 THEN 'WETH'ELSE t2.symbolEND AS symbol_to,COALESCE(b.chain, t.blockchain) AS from_blockchain,COALESCE(b.chain, t2.blockchain) AS to_blockchain,t.decimals AS from_decimals,t2.decimals AS to_decimalsFROM bridge_txns bLEFT JOIN tokens.erc20 t ON b.fromAssetId = t.contract_address AND b.chain = t.blockchainLEFT JOIN tokens.erc20 t2 ON b.toAssetId = t2.contract_address AND b.chain = t2.blockchain),clean_bridge_txns AS (SELECT *,DATE_TRUNC('day', evt_block_time) AS day,CASEWHEN from_decimals IS NULL THEN b.fromAmount / 1e18ELSE b.fromAmount / POW(10, from_decimals)END AS from_token_value,CASEWHEN to_decimals IS NULL THEN b.toAmount / 1e18ELSE b.toAmount / POW(10, to_decimals)END AS to_token_valueFROM bridge_txns_ext b),prices AS (SELECT DATE_TRUNC('day', minute) AS day, symbol, AVG(price) AS price_usdFROM prices.usdGROUP BY 1, 2),final AS (SELECTb.*,from_token_value * p.price_usd AS fromAmount_token_value_usd,