Query editor
WITH vaults AS (SELECT * FROM (VALUES('Yearn OG USDC Morpho Vault', 0x570e2F01D1A73A9632F331b2A1b20982Bb9A3a05, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6, 'ethereum'),('Yearn Morpho Gauntlet USDT Prime Compounder', 0x90338251b14bc88dB94cD74f477153fdD5733Fe3, 0xdac17f958d2ee523a2206206994597c13d831ec7, 'USDT', 6, 'ethereum'),('Yearn Compound V3 WETH Lender', 0xdB92bBBD96a96225cCe2dDbFa7969eC1A6094918, 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, 'WETH', 18, 'ethereum'),('Steakhouse USDC Morpho Vault', 0x1a9cD1E29a127f46F7C3cF08202Dc1649a33102c, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6, 'ethereum'),('Morpho Yearn OG USDC Compounder', 0x4E4eDb00d1b2C6b9a4BA0E20977dbf55fF6392Dd, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6, 'ethereum'),('Morpho Steakhouse USDC Compounder', 0x5e7ffAd167d999152533D6b37Dfb3495B3d6FAAb, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6, 'ethereum'),('Morpho Gauntlet WETH Prime', 0xd854a2eA13B9CA3D1f8523BeadB433D467Db88D8, 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2, 'WETH', 18, 'ethereum'),('Morpho Gauntlet USDC Prime Compounder', 0x1C4Ad88A5D14ca18E26446387fFe98caB6B22e55, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6, 'ethereum'),('Morpho Gauntlet USDC Frontier', 0x6E49dd7276f43E5bf95eD8a486c043cc77aFD041, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6, 'ethereum'),('Gauntlet USDC Prime Morpho Vault', 0x693a0081BA2D84a47269AE518ce02e423AE14E71, 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 'USDC', 6, 'ethereum')) AS t(vault_name, proxy_address, asset_address, asset, asset_decimals, chain)),pricing AS (SELECT DATE_TRUNC('day', minute) AS day, symbol, contract_address, blockchain, AVG(price) AS priceFROM prices.usdWHERE blockchain IN ('ethereum')AND symbol IN ('USDC', 'WETH', 'USDT')GROUP BY 1,2,3,4)SELECT 'Bennu' as provider,evt_block_time, action, vault_name, chain, proxy_address, user, evt_tx_hash, asset, amount, amount_usdFROM (-- Ethereum depositsSELECTl.block_time AS evt_block_time,'Deposit' AS action,v.vault_name,v.chain,v.proxy_address,varbinary_ltrim(topic1) AS user,get_href('https://etherscan.io/tx/' || CAST(l.tx_hash AS VARCHAR), CAST(l.tx_hash AS VARCHAR)) as evt_tx_hash,v.asset,CAST(bytearray_to_uint256(varbinary_substring(data, 1, 32)) AS DOUBLE) / POWER(10, v.asset_decimals) AS amount,CAST(bytearray_to_uint256(varbinary_substring(data, 1, 32)) AS DOUBLE) / POWER(10, v.asset_decimals) * p.price AS amount_usdFROM ethereum.logs lINNER JOIN vaults v ON v.proxy_address = l.contract_address AND v.chain = 'ethereum'LEFT JOIN pricing p ON p.symbol = v.asset AND p.day = DATE_TRUNC('day', l.block_time) AND p.blockchain = 'ethereum'WHERE topic0 = 0xdcbc1c05240f31ff3ad067ef1ee35ce4997762752e3a095284754544f4c709d7 -- depositUNION ALL-- Ethereum withdrawalsSELECTl.block_time AS evt_block_time,'Withdraw' AS action,v.vault_name,v.chain,v.proxy_address,varbinary_ltrim(topic1) AS user,get_href('https://etherscan.io/tx/' || CAST(l.tx_hash AS VARCHAR), CAST(l.tx_hash AS VARCHAR)) as evt_tx_hash,v.asset,-CAST(bytearray_to_uint256(varbinary_substring(data, 1, 32)) AS DOUBLE) / POWER(10, v.asset_decimals) AS amount,-CAST(bytearray_to_uint256(varbinary_substring(data, 1, 32)) AS DOUBLE) / POWER(10, v.asset_decimals) * p.price AS amount_usdFROM ethereum.logs lINNER JOIN vaults v ON v.proxy_address = l.contract_address AND v.chain = 'ethereum'LEFT JOIN pricing p ON p.symbol = v.asset AND p.day = DATE_TRUNC('day', l.block_time) AND p.blockchain = 'ethereum'WHERE topic0 = 0xfbde797d201c681b91056529119e0b02407c7bb96a4a2c75c01fc9667232c8db -- withdraw