0xKofiTraders with Realized Profit
Updated 2024-07-15
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 fantasy_trades AS (
SELECT l.block_timestamp
, l.block_number
, l.tx_hash
, txs.from_address AS user
, CASE WHEN l.origin_function_signature = '0xf66a6799' THEN 'Cancel Order'
WHEN l.origin_function_signature = '0x00cb1eef' THEN 'Sell'
WHEN l.origin_function_signature = '0x0c03ecb3' THEN 'Batch Buy'
END AS event_type
, l.data AS data_raw
, CASE WHEN l.origin_function_signature = '0xf66a6799' THEN l.data
END AS cancelled_order
, CASE WHEN l.origin_function_signature = '0x00cb1eef' THEN '0x' || substr(l.data, 27, 40)
ELSE txs.from_address
END AS buyer
, CASE WHEN l.origin_function_signature = '0x0c03ecb3' THEN '0x' || substr(l.data, 27, 40)
ELSE txs.from_address
END AS seller
, CASE WHEN l.origin_function_signature IN ('0x00cb1eef', '0x0c03ecb3') THEN utils.udf_hex_to_int(substr(l.data, 579, 64)) END AS token_id
, CASE WHEN l.origin_function_signature IN ('0x00cb1eef', '0x0c03ecb3') THEN '0x' || substr(l.data, 283, 40) END AS token_address
, CASE WHEN l.origin_function_signature IN ('0x00cb1eef', '0x0c03ecb3') THEN utils.udf_hex_to_int(substr(l.data, 324, 64))/POWER(10, 18) END AS price
FROM blast.core.fact_event_logs l
INNER JOIN blast.core.fact_transactions txs ON txs.block_number=l.block_number
AND txs.tx_hash=l.tx_hash
WHERE contract_address = '0x56bfb3a51a7a2d4f685e5107cee05a58a0f1ad61'
AND l.block_timestamp >= '2024-05-01 15:00' AND l.block_timestamp < date_trunc('day', current_timestamp)
)
,trading_volumes as (
select
user
, -sum(price) as amount
, 'buy' as direction
from fantasy_trades
where event_type = 'Batch Buy'
group by 1
QueryRunArchived: QueryRun has been archived