MajorM11100 get reserve, roi for 1 address
Updated 2024-10-05
999
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 get ohlcv for 1 address @ https://flipsidecrypto.xyz/studio/queries/41b2bed7-5019-49ae-ac97-2b3abd6e077f
-- obtention des reserves de maniere globales, pour ensuite faire des sous requetes pour avoir les ath et reserves de listing
WITH Reserves AS (
SELECT
TRY_CAST(t1.decoded_log:reserve0::STRING AS DECIMAL(38, 0)) / POWER(10, 18) AS reserve0,
TRY_CAST(t1.decoded_log:reserve1::STRING AS DECIMAL(38, 0)) / POWER(10, 18) AS reserve1,
t1.contract_address,
t1.block_number as block_number,
t1.block_timestamp,
t1.tx_hash,
t1.event_index,
t1.event_name,
ROW_NUMBER() OVER (PARTITION BY t1.contract_address ORDER BY t1.block_number ASC) AS rn
FROM avalanche.core.fact_decoded_event_logs t1
WHERE t1.contract_address = LOWER('0xfc6a7a051e55b4bae02e5da7144b3f22cc7ebfc1')
AND t1.event_name = 'Sync'
ORDER BY t1.block_number ASC, t1.event_index ASC
),
sub_ATHReserves AS (
SELECT DISTINCT
R.contract_address as contract_address_2,
MAX(R.reserve0) OVER (PARTITION BY R.contract_address) AS reserve0_max,
MAX(R.reserve1) OVER (PARTITION BY R.contract_address) AS reserve1_max,
FIRST_VALUE(R.reserve1) OVER (PARTITION BY R.contract_address ORDER BY R.reserve0 DESC, R.block_number DESC) AS reserve1_associated,
FIRST_VALUE(R.reserve0) OVER (PARTITION BY R.contract_address ORDER BY R.reserve1 DESC, R.block_number DESC) AS reserve0_associated
FROM Reserves R
),
TokenInfo AS (
SELECT
LOWER(pool_address) AS contract_address,
tokens:token0::STRING AS token0,
tokens:token1::STRING AS token1
FROM AVALANCHE.defi.dim_dex_liquidity_pools
QueryRunArchived: QueryRun has been archived