Query editor
WITH fees_collection AS (SELECTchain,evt_tx_hash,evt_index,evt_block_dateFROMlifi_multichain.feecollector_evt_feescollectedWHERE_integrator = X'b50b730D81611c1C8139e9B70D262152b895d918'AND evt_block_date >= CURRENT_DATE - INTERVAL '300' DAY),assets_swapped AS (SELECTDISTINCT chain,evt_tx_hash,evt_index,evt_block_date,fromAmount,CAST(fromAssetId AS varchar) AS fromAssetId,evt_tx_from AS userFROMlifi_multichain.lifidiamond_evt_assetswappedWHEREevt_block_date >= CURRENT_DATE - INTERVAL '300' DAY),prices_day AS (SELECTcontract_address_varchar,price,decimals,timestampFROMprices.dayWHEREtimestamp >= CURRENT_DATE - INTERVAL '300' DAY),distinct_prices AS (SELECT DISTINCTcontract_address_varchar,CAST(timestamp AS date) AS price_date,FIRST_VALUE(price) OVER (PARTITION BY contract_address_varchar, CAST(timestamp AS date) ORDER BY ABS(EXTRACT(HOUR FROM timestamp) * 3600 + EXTRACT(MINUTE FROM timestamp) * 60 + EXTRACT(SECOND FROM timestamp)) ASC) AS price,FIRST_VALUE(decimals) OVER (PARTITION BY contract_address_varchar, CAST(timestamp AS date) ORDER BY ABS(EXTRACT(HOUR FROM timestamp) * 3600 + EXTRACT(MINUTE FROM timestamp) * 60 + EXTRACT(SECOND FROM timestamp)) ASC) AS decimals,FIRST_VALUE(timestamp) OVER (PARTITION BY contract_address_varchar, CAST(timestamp AS date) ORDER BY ABS(EXTRACT(HOUR FROM timestamp) * 3600 + EXTRACT(MINUTE FROM timestamp) * 60 + EXTRACT(SECOND FROM timestamp)) ASC) AS timestampFROMprices_day)SELECTa.user,SUM((a.fromAmount / POWER(10, p.decimals)) * p.price) AS total_usd_equivalentFROMassets_swapped aJOINdistinct_prices p ON a.fromAssetId = p.contract_address_varcharAND a.evt_block_date = p.price_dateJOINfees_collection f ONa.chain = f.chainAND a.evt_tx_hash = f.evt_tx_hash--AND a.evt_index = f.evt_index + 1