WITH initial_LP_deposit as (

-- These position are good example to show case the LP range price from narrow, moderate to wide

SELECT

price_upper_0_1_usd-price_lower_0_1_usd as price_range_diff,

case

when nf_token_id = '774671' then 'narrow'

when nf_token_id = '788567' then 'moderate'

else 'wide' end as price_range_tier,

*

FROM ethereum.uniswapv3.ez_lp_actions

WHERE 0=0

and nf_token_id in ('774671', '788567', '777461')

and ez_lp_actions_id in ('7bba0069b5d0b9b5ef9815cf63758a45', 'b32b1ceebfc83cee0a62d986466afe4e', 'aa4ebf017ac427e19eed42810e76e6b0')

),

hourly_ethereum_price as (

SELECT

hour,

open,

high,

low,

close

FROM crosschain.price.fact_prices_ohlc_hourly

WHERE asset_id = 'ethereum' AND provider = 'coingecko'

AND hour >= (SELECT min(date(block_timestamp)) FROM initial_LP_deposit)

),

v3_swaps_txs2emulate_rebalancing_portfolio as (

-- NOTE:

-- V3: Use the transactions to simulation the rebalancing action for rebalancing portfolio, mainly to obtain the token price of the AMM and perform rebalancing accordingly to calculate the token0 and token1 amount

-- V2: Use the transactions to calculate the proposition of token0 and token1 left in LP position, mainly to obtain the token price of the AMM and carry out the v2 LP calculation

SELECT

(SELECT price_range_tier FROM initial_LP_deposit where price_range_tier = 'narrow') as price_range_tier,

(SELECT token1_price FROM initial_LP_deposit where price_range_tier = 'narrow') as initial_LP_price, -- WETH Price when depositing LP

(SELECT price_lower_0_1_usd FROM initial_LP_deposit where price_range_tier = 'narrow') as price_lower_0_1_usd,

(SELECT price_upper_0_1_usd FROM initial_LP_deposit where price_range_tier = 'narrow') as price_upper_0_1_usd,