primo_datasushi_weth_usdc_impermanent_loss_eth
Updated 2022-07-22
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
›
⌄
--Compare impermanent loss on Sushi pools on Mainnet vs. on Polygon, and create visualizations to illustrate the comparison.
--You are free to draw your own conclusions on the comparison analysis, but the goal here is to put your skills to the test and help us develop a TWAP
--methodology that allows for understanding changes in LP value (not adjusted for fee revenue) over time at the deposit level.
--Since each deposit happens at a different time, there must be something specific to each deposit.
--Withdrawals of liquidity can be ignored for now.
--Hints:
--Using Sushi USDC/WETH Pool on Mainnet:
--1. Identify all liquidity deposits between ETH Block Number 14000000 and Block Number 15180000 into the mainnet pool
--2. For each depositor-deposit get the ETH price in USDC at that deposit time (can use hourly_price table as a shortcut, but ideally, create a time weighted average price using SWAPS in the
-- pool within the last 100 blocks of that deposit) and identify their ETH/USDC deposit ratio (how much ETH did they deposit, how much USDC, what is that total value in USDC terms)
--3. Use the depositor-deposit ETH/USDC ratio at time of its deposit (changes for every depositor-deposit) to calculate the impermanent loss of each deposits at Block Number 15180000
--The resulting table may look like this (tweak as needed, we have not done this query before so adjust to fit your analysis):
--BLOCK_NUMBER_OF_DEPOSIT | TX_HASH | Liquidity_Provider | Amount ETH Deposited in Pool | Amount USDC Deposited in Pool | ETH/USDC TWAP price at Block_Number_OF_DEPOSIT |
--ETH/USDC TWAP Price at BLOCK 15180000 | Impermanent Loss as of BLOCK 15180000 (in USDC)
--https://dailydefi.org/tools/impermanent-loss-calculator/
--ethereum
with depositers as (
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
Run a query to Download Data