Query editor
-- Q3 v2: TVL per vault using totalSupply × last known share priceWITH vaults AS (SELECT * FROM dune.silo_finance.query_7331967),share_transfers AS (SELECT 'ethereum' AS chain, contract_address AS vault_address,"from" AS f, "to" AS t, CAST(value AS DECIMAL(38,0)) AS sharesFROM erc20_ethereum.evt_transferWHERE contract_address IN (0x5362D5086FDef73450145492a66F8EBF210c5B9C,0x17367DcC7386f01B99E34Ca67E5071D1cdB53A43)UNION ALLSELECT 'arbitrum', contract_address, "from", "to", CAST(value AS DECIMAL(38,0))FROM erc20_arbitrum.evt_transferWHERE contract_address = 0xd8c989aB5f5b2ABDc76a8D3Acec165300BF30ecDUNION ALLSELECT 'avalanche_c', contract_address, "from", "to", CAST(value AS DECIMAL(38,0))FROM erc20_avalanche_c.evt_transferWHERE contract_address IN (0x1F0570a081FeE0e4dF6eAC470f9d2D53CDEDa1c5,0xF8C100aB4b2d416609b0Ef18105eBf601b2ec84A)),-- totalSupply = mints (from=0x0) minus burns (to=0x0)total_supply AS (SELECTchain,vault_address,SUM(CASE WHEN f = 0x0000000000000000000000000000000000000000 THEN shares ELSE 0 END)- SUM(CASE WHEN t = 0x0000000000000000000000000000000000000000 THEN shares ELSE 0 END)AS total_shares_rawFROM share_transfersGROUP BY 1, 2),-- Most recent deposit gives us assets/shares ratio = share pricelatest_deposits AS (SELECTchain,vault_address,assets,shares,ROW_NUMBER() OVER (PARTITION BY chain, vault_address ORDER BY block_time DESC) AS rnFROM dune.silo_finance.query_7331985WHERE action = 'deposit' AND shares > 0),share_price AS (SELECTchain,vault_address,CAST(assets AS DOUBLE) / CAST(shares AS DOUBLE) AS price_per_shareFROM latest_depositsWHERE rn = 1)SELECTv.display_name,v.manager,v.chain,v.asset_symbol,CAST(ts.total_shares_raw AS DOUBLE) / POWER(10, v.decimals) AS shares_outstanding,