mucryptoAverage and median 24 hour change 2023-08-30 09:21 PM
    Updated 2023-12-29
    with resps as
    (select defillama.get('/v2/historicalChainTvl', {}) as resp),

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

    select
    avg(coalesce((c1.tvl - c2.tvl) / c2.tvl * 100, 0)) as average_24_h_change,
    median(coalesce((c1.tvl - c2.tvl) / c2.tvl * 100, 0)) as median_24_h_change
    from rn as c1
    left join rn as c2
    on c1.rn = c2.rn - 1
    QueryRunArchived: QueryRun has been archived