commanderjoseph-761gwXLong/Short Opened and Closed in last 24hr
Updated 2024-09-24
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
total_position1
AS
(
SELECT
block_timestamp,
tx_hash,
origin_from_address,
utils.udf_hex_to_int(SUBSTR(topics[2], 3, 64)) AS position,
utils.udf_hex_to_int(SUBSTR(topics[3], 3, 64)) AS open_close
FROM
berachain.testnet.fact_event_logs
WHERE
origin_to_address = lower('0xb3395EeeA7701E0037bBC6Ab52953C6fB0c3326c')
AND
topics[0] = lower('0x946036ec838a96db6089510038e2de4a934fb985f2ba9ef85dd4166f622db233') -- LONG/SHORT
AND
origin_from_address != '0x0000000000000000000000000000000000000000'
AND
tx_succeeded = 'TRUE'
AND
block_timestamp > CURRENT_TIMESTAMP - INTERVAL '1 day'
)
SELECT
block_timestamp,
origin_from_address,
CASE
WHEN position = 0 AND open_close = 0 THEN 'short_close' --END AS trading_status,
WHEN position = 0 AND open_close = 1 THEN 'short_open' -- END AS trading_status,
WHEN position = 1 AND open_close = 0 THEN 'long_close'-- END AS trading_status
WHEN position = 1 AND open_close = 1 THEN 'long_open'
END AS trading_status,
tx_hash
FROM
total_position1
QueryRunArchived: QueryRun has been archived