polarityPremium and volume
Updated 2025-02-13
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
›
⌄
WITH raw_data AS (
SELECT
block_timestamp,
tx_hash,
origin_from_address,
topics,
data
FROM berachain.testnet.fact_event_logs
WHERE origin_to_address = LOWER('0x8B1A76B48a284047f3BF1Ee15723BBb54C49c63D')
AND block_timestamp >= '2024-08-27'
AND tx_succeeded = 'TRUE'
)
SELECT
DATE(block_timestamp) AS date,
SUM(utils.udf_hex_to_int(substr(data, 131, 64)) / pow(10, 18)) AS total_size_usd,
SUM(utils.udf_hex_to_int(substr(data, 195, 64)) / pow(10, 18)) AS total_premium,
SUM(SUM(utils.udf_hex_to_int(substr(data, 131, 64)) / pow(10, 18))) OVER (ORDER BY DATE(block_timestamp)) AS cumulative_size_usd,
SUM(SUM(utils.udf_hex_to_int(substr(data, 195, 64)) / pow(10, 18))) OVER (ORDER BY DATE(block_timestamp)) AS cumulative_premium,
FROM raw_data
WHERE POSITION('0xe6ba045508353d28ffe727d7e1d54c14bb77665dd60636928b88674a9c7b4260' IN topics) > 0
GROUP BY DATE(block_timestamp)
ORDER BY date DESC
QueryRunArchived: QueryRun has been archived