mucrypto2023-08-29 02:03 AM
    Updated 2023-08-28
    with resps as
    (select defillama.get('/protocols', {}) as resp),

    ttvl as (
    select
    value:category::string as category,
    value:tvl::number as tvl
    from resps,
    lateral flatten (input => resp:data)),

    combined as (select
    category,
    sum(tvl) as combined_tvl
    from ttvl
    where category != 'CEX'
    and category != 'Chain'
    and tvl != 0
    group by 1
    order by 2 desc),

    total as (
    select
    combined_tvl as joined_tvl,
    sum(combined_tvl) as total_combined_tvl
    from combined
    group by 1)

    select
    c.category,
    c.combined_tvl,
    c.combined_tvl / t.total_combined_tvl * 100 as share_of_tvl
    from combined c
    right join total t
    on c.combined_tvl = t.joined_tvl
    Run a query to Download Data