hbd19942. NEAR/USD
Updated 2023-02-23
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
›
⌄
with base as ((select
'Polygon' as blockchain,
BLOCK_TIMESTAMP,
tx_hash,
concat('0x',SUBSTR(data, 27 + (0*64), 40)) as trader,
case
when ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (6*64), 64)) = '1' then 'Long'
else 'Short' end as long_or_short,
case
when ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (10*64), 64)) = '1' then 'Open'
else 'Close' end as type,
ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (1*64), 64)) as PairIndex,
ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (5*64), 64)) as open_price,
ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (11*64), 64)) as price,
ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (7*64), 64)) as leverage,
case
when long_or_short = 'Short' then ((-1) * (((price - open_price)/(open_price))*leverage))
else (((price - open_price)/(open_price))*leverage) end as percentProfit,
ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (13*64), 64))/pow(10,18) as position_size_dai,
(position_size_dai * percentProfit) as pnl,
(position_size_dai * leverage) as volume
from polygon.core.fact_event_logs
where topics[0] = '0x2739a12dffae5d66bd9e126a286078ed771840f2288f0afa5709ce38c3330997'
and ORIGIN_FUNCTION_SIGNATURE = '0x4ab0d190'
and block_number >= '39173685')
union
(select
'Arbitrum' as blockchain,
BLOCK_TIMESTAMP,
tx_hash,
concat('0x',SUBSTR(data, 27 + (0*64), 40)) as trader,
case
when ethereum.public.udf_hex_to_int(SUBSTR(data, 3 + (6*64), 64)) = '1' then 'Long'
else 'Short' end as long_or_short,
case
Run a query to Download Data