SELECT date(block_timestamp) deposit_dt

, block_number deposit_block

, tx_hash deposit_tx_hash

, origin_from_address deposit_wallet

, sum(case when symbol='WETH' then amount else null end) deposit_weth_amount

, sum(case when symbol='USDC' then amount else null end) deposit_usdc_amount

, sum(amount_usd) deposit_usd_total

, avg(case when symbol='WETH' then token_price else null end) deposit_weth_price

FROM ethereum.core.ez_token_transfers

where date(block_timestamp) >= date('2022-01-10')

and block_number >= 14000000 and block_number <= 15180000

and to_address = '0x397ff1542f962076d0bfe58ea045ffa2d347aca0' --USDC/WETH sushiswap pool_address

and origin_function_signature = '0xf305d719' -- addLiquidityETH

group by 1,2,3,4

),

final_price as (

--- avg USDC/WETH price from block 15179900-15180000

select avg((case when symbol_in='USDC' then amount_in else amount_out end) / (case when symbol_out='WETH' then amount_out else amount_in end)) final_weth_price

FROM ethereum.sushi.ez_swaps

where block_timestamp >= CURRENT_DATE - 5

and block_number >= 15179900 and block_number <= 15180000

and contract_address = '0x397ff1542f962076d0bfe58ea045ffa2d347aca0'

),

results as (

select *

, final_price.final_weth_price

, (deposit_usdc_amount + (deposit_weth_amount * final_price.final_weth_price)) final_total_baseline_usd

, ((deposit_usdc_amount * deposit_weth_amount) / sqrt(deposit_usdc_amount * deposit_weth_amount* final_price.final_weth_price)) final_weth_amount

, ((deposit_usdc_amount * deposit_weth_amount) / final_weth_amount) final_usdc_amount

, ((final_weth_amount * final_price.final_weth_price) + final_usdc_amount) final_total_depositer_usd

, (1 - final_total_depositer_usd / final_total_baseline_usd)*100 impermanent_loss

from depositers, final_price

select *

from results