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