samNear swap transactions - raw amount and denom
Updated 2022-08-03Copy 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
›
⌄
with price_list as (
select
avg(price) as prices,
token_address, symbol, decimals
from ethereum.core.fact_hourly_token_prices
where hour::date = '2022-08-02'
group by token_address, symbol, decimals
),
swap_raw as (
select
tx:receipt[0]:outcome:logs[0] as logs,
split(logs, ' ') as split,
split[0] as swap_string,
split[1] as token_in_amount,
case when length(split[2]) > 40 then concat ( '0x', split(split[2], '.')[0])
else split[2] end as token_in_denom ,
split[4] as token_out_amount,
case when length(split[5]) > 40 then concat ( '0x', split(split[5], '.')[0])
else split[5] end as token_out_denom
from near.core.fact_transactions
where block_timestamp::date = '2022-01-15'
and tx_hash = 'DX2CmfVThGWpsmCv2vTZBJGWS3gYuWfcvZUAsRtYZU8D'
)
select
token_in_amount,
coalesce (p.symbol,token_in_denom) as token_in_denom_adj ,
token_out_amount,
coalesce (q.symbol,token_out_denom) as token_out_denom_adj
from swap_raw
Run a query to Download Data