freemartianTotal Trade Fee
Updated 2023-06-22
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 open_table AS (
SELECT
block_timestamp,
tx_hash,
topics[1] AS positionid,
regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,
CONCAT('0x', substr(topics[2] :: STRING, 27, 42)) AS user_address,
ethereum.public.udf_hex_to_int(topics[3] :: STRING) AS productid,
ethereum.public.udf_hex_to_int(segmented_data [0] :: STRING) AS direction,
avg(ethereum.public.udf_hex_to_int(segmented_data [3] :: STRING)/pow(10,8)) AS margin,
avg(ethereum.public.udf_hex_to_int(segmented_data [4] :: STRING)/pow(10,8)) AS leverage,
(margin*leverage)/1000 AS fee,
ethereum.public.udf_hex_to_int(segmented_data [7] :: STRING)/pow(10,8) AS funding,
0 AS PNL,
0 AS liquidation_status,
cast('Open' AS STRING) AS type
FROM optimism.core.fact_event_logs
WHERE topics[0] = '0xf0179f06d5592030053869d8d0ae508420786cda1b88f04f1611215f029a35ab'
--AND user_address = '0x39fe1beef05ded01d35c2b97e3fb6939d4f0aee3'
GROUP BY 1,2,3,4,5,6,7,11),
direction_table AS (
SELECT positionid, direction
FROM open_table
),
close_table AS (
SELECT
block_timestamp,
tx_hash,
regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data,
topics[1] AS positionid,
CONCAT('0x', substr(topics[2] :: STRING, 27, 42)) AS user_address,
Run a query to Download Data