Query editor
-- Base architecture, Vault mapping, and price feeds leveraged from community queries (Query 4660088 / 4660097).-- Extended and modified for Collateral Risk & Capital Allocation analysis by Joan A. SeguĂ.-- Objective: Map protocol architecture and evaluate Capital Inefficiency (Idle Stake vs. Delegated Stake) across Symbiotic Vaults.WITH named_vaults(address, name) AS (SELECT * FROM query_4660088),all_vaults AS (SELECT output_entity_ as address,coalesce(n.name, 'Unknown vault') as name,case when varbinary_ltrim(varbinary_substring(data, 12, 21)) = 0x20 then varbinary_ltrim(varbinary_substring(data, 33, 32)) else varbinary_ltrim(varbinary_substring(data, 12, 21)) end as token,t.symbol,decimalsFROM symbiotic_ethereum.vaultfactory_call_create cLEFT JOIN named_vaults n ON c.output_entity_ = n.addressLEFT JOIN (select blockchain, contract_address, symbol, decimals from prices.tokensunion select 'ethereum', 0x78c6b27be6db520d332b1b44323f94bc831f5e33, 'lvlwaUSDC', 6union select 'ethereum', 0xb723377679b807370Ae8615ae3E76F6D1E75a5F2, 'lvlwaUSDT', 6union select 'ethereum', 0x0a6E7Ba5042B38349e437ec6Db6214AEC7B35676, 'SWELL', 18union select 'ethereum', 0x20157DBAbb84e3BBFE68C349d0d44E48AE7B5AD2, 'iBTC', 8union select 'ethereum', 0xfC385A1dF85660a7e041423DB512f779070FCede, 'ZKL', 18union select 'ethereum', 0x06325440d014e39736583c165c2963ba99faf14e, 'stCRV', 18union select 'ethereum', 0x4e9c3ef86bc1ca1f0bb5c7662861ee93350568, 'uniBTC', 8union select 'ethereum', 0xac3e018457b222d93114458476f3e3416abbe38f, 'sfrxETH', 18) t ON t.contract_address = case when varbinary_ltrim(varbinary_substring(data, 12, 21)) = 0x20 then varbinary_ltrim(varbinary_substring(data, 33, 32)) else varbinary_ltrim(varbinary_substring(data, 12, 21)) end and blockchain = 'ethereum'),vaults_params AS (SELECT output_vault as vault,case when json_extract_scalar(params, '$.delegatorIndex') = '0' then 'NetworkRestakeDelegator'when json_extract_scalar(params, '$.delegatorIndex') = '1' then 'FullRestakeDelegator'when json_extract_scalar(params, '$.delegatorIndex') = '2' then 'OperatorSpecificDelegator'when json_extract_scalar(params, '$.delegatorIndex') = '3' then 'OperatorNetworkSpecificDelegator'end delegator_type,case when json_extract_scalar(params, '$.withSlasher') = 'false' then '--'when json_extract_scalar(params, '$.withSlasher') = 'true' and json_extract_scalar(params, '$.slasherIndex') = '0' then 'Slasher'when json_extract_scalar(params, '$.withSlasher') = 'true' and json_extract_scalar(params, '$.slasherIndex') = '1' then 'VetoSlasher'end as slasher_type,paramsFROM symbiotic_ethereum.vaultconfigurator_call_createWHERE call_success),dates AS (WITH day_seq AS (SELECT (sequence(date'2025-01-28', current_date, interval '1' day)) day)SELECT days.dayFROM day_seqCROSS JOIN unnest (day) AS days (day)),dates_per_vaults AS (SELECT day, address as vault, name as vault_name, token, symbol, decimalsFROM datesJOIN all_vaults ON 1 = 1),