mucryptoTVL market share categorized 2023-09-02 10:14 AM
    Updated 2023-12-19
    with resps as
    (select defillama.get('/protocols', {}) as resp),

    tvl as (
    select
    value:category 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),

    market_share as (
    select
    category,
    cast(combined_tvl / total_combined_tvl * 100 as decimal(38,2)) as tvl_market_share
    from combined
    inner join total_combined)

    select
    case
    when tvl_market_share > 20 then '>20'
    when tvl_market_share >= 15.00 and tvl_market_share <= 20 then '15.00-20'
    when tvl_market_share >= 10.00 and tvl_market_share <= 14.99 then '10.00-14.99'
    Run a query to Download Data