MajorM111version finale
Updated 2024-10-05Copy Reference Fork
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
›
⌄
SELECT
lp.creation_block as creation_block_pool,
lp.creation_time as creation_time_pool,
(select count(tx_hash) from avalanche.core.ez_decoded_event_logs ab where contract_address = lp.pool_address and ab.event_name = 'Swap') as number_of_tx,
t1.symbol as symbol_token,
t1.name as name_token,
lp.pool_name,
lp.pool_address,
t1.address as address_token,
lp.tokens:token0::STRING AS token0,
lp.tokens:token1::STRING AS token1,
lp.platform,
lp.factory_address as factory_address_pool,
--t1.created_block_number as created_block_number_token,
--t2.created_block_number as created_block_number_token_2,
CASE
WHEN lp.tokens:token1::STRING = LOWER('0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7') THEN t1.created_block_number
ELSE t2.created_block_number
END AS created_block_number_token,
lp.creation_tx creation_tx_pool,
t1.created_tx_hash as created_tx_hash_token
FROM avalanche.defi.dim_dex_liquidity_pools lp
JOIN avalanche.core.dim_contracts t1 ON LOWER(lp.tokens:token0::STRING) = LOWER(t1.address)
JOIN avalanche.core.dim_contracts t2 ON LOWER(lp.tokens:token1::STRING) = LOWER(t2.address)
WHERE lp.platform = 'trader-joe-v1'
AND t1.symbol <> ''
AND t1.name <> ''
AND lp.creation_time > '2022-12-01 01:01:01.000'
AND lp.pool_address IN (
--LOWER('0x41ab86eecbd110a82ca602d032a461f453066f1e'), -- coq
LOWER('0x0368780d62d7d6f75f943ecbccaa7bdbace57e49'), -- wang
LOWER('0xf419fd326e733b191b7f604f8126f3c67792720b') -- sonic
)
-- filtre en fonction de la pool pour avoir que 1 seul projet
QueryRunArchived: QueryRun has been archived