mucryptoTotal share of positive vs. negative Month-on-Month change in average TVL 2023-09-15 05:56 PM
    Updated 2023-12-20
    with resps as
    (select defillama.get('/v2/historicalChainTvl', {}) as resp),

    rn as (
    select
    value:date::timestamp as date,
    value:tvl as tvl
    from resps,
    lateral flatten (input => resp:data)),

    total as (
    select
    date_trunc('month', date) as month,
    avg(tvl) as "Average TVL",
    row_number() over (order by month desc) as rn
    from rn
    group by 1),

    list as (
    select
    c1.month,
    c1."Average TVL",
    coalesce((c1."Average TVL" - c2."Average TVL") / c2."Average TVL" * 100, 0) as "Month-on-Month change in average TVL",
    case
    when "Month-on-Month change in average TVL" > 0 then 'Positive MoM Change'
    when "Month-on-Month change in average TVL" < 0 then 'Negative MoM Change'
    end as category
    from total as c1
    left join total as c2
    on c1.rn = c2.rn - 1)

    select
    category,
    count(1) as instances
    from list
    where category is not null
    Run a query to Download Data