with resps as
(select defillama.get('/protocols', {}) as resp),
tvl as (
select
value:category::string as category,
value:tvl as tvl
from resps,
lateral flatten (input => resp:data)),
combined as (select
category,
sum(tvl) as combined_tvl
from tvl
where category != 'CEX'
and category != 'Chain'
group by 1
order by 2 desc),
total_combined as (
select
sum(combined_tvl) as total_combined_tvl
from combined)
select
category,
cast(combined_tvl / total_combined_tvl * 100 as decimal(38,2)) as tvl_market_share
from combined
inner join total_combined
order by 2 desc