Query editor
WITH all_days AS (-- Generate a sequence of days from June 15, 2024 to NOW()SELECT days.time AS dayFROM (SELECT sequence(DATE '2024-06-15',CAST(DATE_TRUNC('day', NOW()) AS DATE),INTERVAL '1' DAY) AS time) AS seqCROSS JOIN unnest(time) AS days(time)),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),-- Daily deposits and withdrawals for all vaultsdaily_flows AS (SELECTDATE_TRUNC('day', block_time) AS day,vault_name,proxy_address,chain,SUM(CASE WHEN amount_usd > 0 THEN amount_usd ELSE 0 END) AS daily_inflow,SUM(CASE WHEN amount_usd < 0 THEN ABS(amount_usd) ELSE 0 END) AS daily_outflow,SUM(amount_usd) AS daily_net_flowFROM (-- Ethereum DepositsSELECTblock_time,v.vault_name,v.proxy_address,v.chain,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