gaonipLPP 2.1 - top pools with highest HODL return copy
Updated 2023-10-23
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 vivianngdora / LPP 2.1 - top pools with highest HODL return @ https://flipsidecrypto.xyz/vivianngdora/q/AarWfvvSVjqk/lpp-2.1---top-pools-with-highest-hodl-return
with list_pools as (
select
pool_name,
pool_address
from ethereum.uniswapv3.ez_pool_stats
where
date(block_timestamp) = current_date - 1 and
token1_balance_usd >0 and token0_balance_usd >0 ),
first_price as (
select pool_name,pool_address,
TOKEN0_PRICE as first_token0_price,
TOKEN1_PRICE as first_token1_price,
rank() over(partition by pool_address order by block_timestamp asc) as rank_price
from
ethereum.uniswapv3.ez_lp_actions
where pool_address in (select pool_address from list_pools)
qualify rank_price = 1 ),
latest_price as (
select pool_name,pool_address,
TOKEN0_PRICE as latest_token0_price,
TOKEN1_PRICE as latest_token1_price,
rank() over(partition by pool_address order by block_timestamp desc) as rank_price
from
ethereum.uniswapv3.ez_lp_actions
where pool_address in (select pool_address from list_pools)
qualify rank_price = 1 )
select
a.pool_name,a.pool_address,
((latest_token0_price / first_token0_price) + (latest_token1_price / first_token1_price)) / 2 as HODL_return,
((2 * SQRT((latest_token0_price/latest_token1_price) / (first_token0_price/first_token1_price)))/(1 + ((latest_token0_price/latest_token1_price) / (first_token0_price/first_token1_price)))) - 1 as Impermanent_Loss,
first_token0_price,
latest_token0_price,
first_token1_price,
latest_token1_price
Run a query to Download Data