cybergenlab[Ecosystem metrics] Network TVL
Updated 2024-11-16
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
⌄
-- forked from [Ecosystem metrics] Network Big Numbers metrics @ https://flipsidecrypto.xyz/studio/queries/2f1018cf-4f60-4ecc-aa99-63b757b90080
--Get all metrics for Top Ethereum ecosystem metrices
/* Existing sectors in dim_labels
dex, dapp, operator, games, chadmin, bridge, token, nft, flotsam, cex, defi
*/
with defiLlama as (
select
livequery.live.udf_api(
'https://api.llama.fi/v2/historicalChainTvl/Ethereum'
) as response
)
, tvl_no_lst as (
select
date,
avg(tvl) as tvl_no_lst
from(
select
date_trunc('month',to_date(tvl.value:date::string)) as date,
tvl.value:tvl as TVL -- This TVL excluding liquid staking according to Defillama
from defiLlama
join lateral flatten (input => response:data) tvl
)
where date >= dateadd(year, -1, date_trunc('month',current_date()))
and date < date_trunc('month',current_date())
group by 1
)
, tvl_lst as (
select
date_trunc('month', date) as date,
avg(tvl_usd) as tvl_lst
QueryRunArchived: QueryRun has been archived