Abolfazl_771025Untitled Query
Updated 2022-11-29
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 openocean as (with txn_main as (select
tx_hash,
rank() over (partition by tx_hash order by event_index desc ) as rank
from avalanche.core.fact_event_logs
where origin_to_address = lower('0x6352a56caadC4F1E25CD6c75970Fa768A3304e64')
and event_name = 'Swap'
qualify rank = 1
),txn as (select
b.block_timestamp,
b.origin_from_address,
b.tx_hash,
b.contract_address,
b.event_inputs,
rank() over (partition by b.tx_hash order by b.event_index) as rank
from txn_main a join avalanche.core.fact_event_logs b on a.tx_hash = b.tx_hash
and b.event_name = 'Transfer'
and b.origin_function_signature = '0x90411a32'
order by b.tx_hash, b.event_index, rank
),swap as (select
a.block_timestamp,
a.origin_from_address,
a.tx_hash,
a.contract_address as from_token,
b.contract_address as to_token,
a.event_inputs:value as token_amount_in
from txn a join txn b on a.tx_hash = b.tx_hash and a.rank = 1 and b.rank = 2
),swap_txn as (select
block_timestamp,
tx_hash,
origin_from_address,
token_amount_in/pow(10,18) as amount
from swap
)
select
date_trunc('day',block_timestamp) as date,
'openocean' as "Dex",
Run a query to Download Data