freemartianDaily & Cumulative Pool PNL
Updated 2023-06-22
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
›
⌄
-- forked from aa67e10c-4a38-4141-be3c-58ea6843aee7
with open_table AS (
SELECT
block_timestamp,
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
FROM optimism.core.fact_event_logs
WHERE topics[0] = '0xf0179f06d5592030053869d8d0ae508420786cda1b88f04f1611215f029a35ab'
--AND user_address = '0x39fe1beef05ded01d35c2b97e3fb6939d4f0aee3'
GROUP BY 1,2,3,4,5,6),
direction_table AS (
SELECT positionid, direction
FROM open_table
),
close_table AS (
SELECT
block_timestamp,
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,
ethereum.public.udf_hex_to_int(topics[3] :: STRING) AS productid,
d.direction,
avg(ethereum.public.udf_hex_to_int(segmented_data [2] :: STRING)/pow(10,8)) AS margin,
avg(ethereum.public.udf_hex_to_int(segmented_data [3] :: STRING)/pow(10,8)) AS leverage,
ethereum.public.udf_hex_to_int(segmented_data [4] :: STRING)/pow(10,8) AS fee
FROM optimism.core.fact_event_logs l JOIN direction_table d on l.topics[1] = d.positionid
WHERE topics[0] = '0x980658f6b65b51ce7d353bc2f9110e33617a69ab8dd85ed76e70546542ebea25'
Run a query to Download Data