Abbas_ra21LPs Count
Updated 2023-10-19
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
›
⌄
with main AS (select
BLOCK_TIMESTAMP,
TX_HASH,
'Arbitrum' AS chain,
POOL_NAME,
POOL_ADDRESS,
ORIGIN_FROM_ADDRESS AS LP,
case when EVENT_NAME='Mint' then 'Increase' else 'Decrease' end AS Action,
(DECODED_LOG:amount0) / power(10, A.DECIMALS:token0) AS amount0,
amount0 * B.price AS amount0_USD,
(DECODED_LOG:amount1) / power(10, A.DECIMALS:token1) AS amount1,
amount1 * C.price AS amount1_USD
from
arbitrum.core.ez_decoded_event_logs
inner join arbitrum.defi.dim_dex_liquidity_pools A on POOL_ADDRESS = CONTRACT_ADDRESS
and PLATFORM = 'uniswap-v3'
left join arbitrum.price.ez_hourly_token_prices B on TOKENS:token0 = B.TOKEN_ADDRESS
and date_trunc('Hour', block_timestamp) = B.HOUR
left join arbitrum.price.ez_hourly_token_prices C on TOKENS:token1 = C.TOKEN_ADDRESS
and date_trunc('Hour', block_timestamp) = C.HOUR
where
ORIGIN_TO_ADDRESS = lower('0xC36442b4a4522E871399CD717aBDD847Ab11FE88')
and EVENT_NAME in ('Mint','Burn')
union ALL
select
BLOCK_TIMESTAMP,
TX_HASH,
'Optimism' AS chain,
POOL_NAME,
POOL_ADDRESS,
ORIGIN_FROM_ADDRESS AS LP,
case when EVENT_NAME='Mint' then 'Increase' else 'Decrease' end AS Action,
(DECODED_LOG:amount0) / power(10, A.DECIMALS:token0) AS amount0,
amount0 * B.price AS amount0_USD,
(DECODED_LOG:amount1) / power(10, A.DECIMALS:token1) AS amount1,
amount1 * C.price AS amount1_USD
Run a query to Download Data