Query editor
-- LBC-DENOM (ETH): worst 7-day net outflow (USD) per vault, transfer-basedWITH params AS (SELECT DATE '2024-01-01' AS start_date),-- Distinct ETH vaults from your balances tablevaults AS (SELECT DISTINCTvault_addressFROM dune.veda.result_vaults_balancesWHERE blockchain = 'ethereum'),-- ERC-20 transfers touching those vaults (decoded logs expose evt_* columns)xfers AS (SELECTt.contract_address AS token_address,t."from" AS from_addr,t."to" AS to_addr,t.value AS value_raw,t.evt_block_time AS tsFROM erc20_ethereum.evt_Transfer tWHERE t.evt_block_time >= (SELECT start_date FROM params)AND (t."from" IN (SELECT vault_address FROM vaults)OR t."to" IN (SELECT vault_address FROM vaults))),-- Token decimals + daily USD price (curated prices use `timestamp`)meta AS (SELECTe.contract_address AS token_address,COALESCE(e.decimals, 18) AS decimalsFROM tokens.erc20 eWHERE e.blockchain = 'ethereum'),px_day AS (SELECTp.contract_address AS token_address,DATE(p.timestamp) AS day,p.price AS price_usdFROM prices.day pWHERE p.blockchain = 'ethereum'),-- Per-vault, per-token, per-day USD in/outflows_token_day AS (SELECT'ethereum' AS blockchain,COALESCE(vin.vault_address, vout.vault_address) AS vault_address,DATE(x.ts) AS day,x.token_address,-- amount in raw -> units -> USDSUM(CASE WHEN x.to_addr = COALESCE(vin.vault_address, vout.vault_address)THEN (x.value_raw / POW(10, m.decimals)) * COALESCE(p.price_usd, 0.0)ELSE 0.0 END) AS in_usd,SUM(CASE WHEN x.from_addr = COALESCE(vin.vault_address, vout.vault_address)